Verity Pickle...
Well..I've gone and gotten myself into some trouble with a database design (mentioned it before) and Verity Searching. It wasn't planned, but it's turned out that we need to do a Verity search on a database. There is one table of casinos, and several other tables of different casino games. A Poker Table, Blackjack table, Slots table etc. all containing the different types of Poker games, Blackjack games etc. that the site tracks. The problem, is that in the tblCasinos table, there are Memo fields, with comma delimited lists of Poker games, blackjack games, Slots games etc. So tblcasinos.poker might be = 34,45,1,10,3 Which would correspond to the tblpoker.pokerid in the Poker table. Now. I want to build a Verity search of this database, that will bring back TblCasinos.Casinoid keys if the person enters say Caribbean Style (a type of Poker game). I've sat and thought about it, messed around with a few SQL queries...but I'm really totally stuck as to how to go about this. Does anyone who has more experience with Verity have any ideas? And yes, there is NO need to tell me that having memo fields with comma delimited lists of values was not the best way to design the database :) -Gel ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190112 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Verity Pickle...
Heh... Although I know there's NO need to tell you this, avoiding comma delimited lists would prevent the need to poke people with your poker pickle. :-P That being said, the obligatory pickle-poking may not be as bad as you think... give this query a poke... err... I mean peek... select casinoid from tblCasinos where ',' + poker + ',' like '%,#pokerid#,%' ike -- player poking pickle-o-phile > Well..I've gone and gotten myself into some trouble with a > database design (mentioned it before) and Verity > Searching. > It wasn't planned, but it's turned out that we need to do > a Verity search on a database. > There is one table of casinos, and several other tables of > different casino games. > A Poker Table, Blackjack table, Slots table etc. all > containing the different types of Poker games, Blackjack > games etc. that the > site tracks. > The problem, is that in the tblCasinos table, there are > Memo fields, with comma delimited lists of Poker games, > blackjack games, > Slots games etc. > So tblcasinos.poker might be = 34,45,1,10,3 > Which would correspond to the tblpoker.pokerid in the > Poker table. > Now. I want to build a Verity search of this database, > that will bring back TblCasinos.Casinoid keys if the > person enters say > Caribbean Style (a type of Poker game). > I've sat and thought about it, messed around with a few > SQL queries...but I'm really totally stuck as to how to go > about this. > Does anyone who has more experience with Verity have any > ideas? > And yes, there is NO need to tell me that having memo > fields with comma delimited lists of values was not the > best way to design the > database :) > -Gel s. isaac dealey 954.927.5117 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://macromedia.breezecentral.com/p49777853/ http://www.sys-con.com/story/?storyid=44477&DE=1 http://www.sys-con.com/story/?storyid=45569&DE=1 http://www.fusiontap.com ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190117 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Verity Pickle...
That's cool if I just wanted to find out whether the tblcasino.poker field was in a list. But consider that I want to have one query where I join the tblcasinos table to the other sub-tables containing the descriptions of the game types. For purposes of creating a Verity Collection from the database. So of course Select tblCasinos.casinoid FROM tblCasinos,tblPoker WHERE tblCasinos.poker = tblpoker.pokerid Won't work in this case, because of the comma delimited list problem. Damn I know there has to be a workaround but I can't think what the heck it is. > >give this query a poke... err... I mean peek... > >select casinoid from tblCasinos >where ',' + poker + ',' like '%,#pokerid#,%' > >ike >-- player poking pickle-o-phile ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190122 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Verity Pickle...
> That's cool if I just wanted to find out whether the > tblcasino.poker field was in a list. You mean if a tblPoker.pokerid field is in a tblCasio.poker list... > But consider that I want to have one query where I join > the tblcasinos table to the other sub-tables containing > the descriptions of the game types. > For purposes of creating a Verity Collection from the > database. > So of course > Select tblCasinos.casinoid > FROM tblCasinos,tblPoker > WHERE tblCasinos.poker = tblpoker.pokerid > Won't work in this case, because of the comma delimited > list problem. Um... except that this isn't the query I gave you... but I see your point -- apparently my original post wasn't very clear... here's a minor modification to join those 2 tables: select tblCasinos.casinoid, tblPoker.pokerid from tblCasinos, tblPoker where ',' + tblCasinos.poker + ',' like '%,' + tblPoker.pokerid + ',%' You may need to use convert(varchar,tblPoker.pokerid) if pokerid is an integer column in sql server or something similar if this is a different database -- but that basic structure will give you the needed data. > Damn I know there has to be a workaround but I can't think > what the heck it is. >> >>give this query a poke... err... I mean peek... >> >>select casinoid from tblCasinos >>where ',' + poker + ',' like '%,#pokerid#,%' >> >>ike >>-- player poking pickle-o-phile s. isaac dealey 954.927.5117 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://macromedia.breezecentral.com/p49777853/ http://www.sys-con.com/story/?storyid=44477&DE=1 http://www.sys-con.com/story/?storyid=45569&DE=1 http://www.fusiontap.com ~| Purchase Contribute 3 from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://www.houseoffusion.com/banners/view.cfm?bannerid=53 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190124 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Verity Pickle...
Ok, after a lot of messing around I have come up with this: --- select CStr(tblpoker.pokerid) + '|' + tblCasinos.casinoid AS CasinoID,tblpoker.pokerdesc from tblCasinos, tblPoker where ',' + tblCasinos.poker + ',' like '%,' + CStr(tblPoker.pokerid) + ',%' UNION select CStr(tblslots.slotsid) + '|' + tblCasinos.casinoid AS casinoid,tblslots.slotsdesc As pokerdesc from tblCasinos,tblslots where ',' + tblCasinos.slots + ',' like '%,' + CStr(tblSlots.slotsid) + ',%' UNION select CStr(tblblackjack.blackjackid) + '|' + tblCasinos.casinoid AS CasinoID,tblblackjack.blackjackdesc AS pokerdesc from tblCasinos,tblblackjack where ',' + tblCasinos.blackjack + ',' like '%,' + CStr(tblblackjack.blackjackid) + ',%' UNION select CStr(tblcardgames.cardsid) + '|' + tblCasinos.casinoid AS CasinoID,tblcardgames.cardsdesc AS pokerdesc from tblCasinos,tblcardgames where ',' + tblCasinos.cgames + ',' like '%,' + CStr(tblcardgames.cardsid) + ',%' UNION select CStr(tblpslots.pslotsid) + '|' + tblCasinos.casinoid AS CasinoID,tblpslots.pslotsdesc AS pokerdesc from tblCasinos,tblpslots where ',' + tblCasinos.progressiveslots + ',' like '%,' + CStr(tblpslots.pslotsid) + ',%' UNION select CStr(tblroulette.rouletteid) + '|' + tblCasinos.casinoid AS CasinoID,tblroulette.roulettedesc AS pokerdesc from tblCasinos,tblroulette where ',' + tblCasinos.roulette + ',' like '%,' + CStr(tblroulette.rouletteid) + ',%' UNION select CStr(tblsoftware.softid) + '|' + tblCasinos.casinoid AS CasinoID,tblsoftware.softdesc AS pokerdesc from tblCasinos,tblsoftware where ',' + tblCasinos.gamingsoftware + ',' like '%,' + CStr(tblsoftware.softid) + ',%' UNION select CStr(tbltypes.typeid) + '|' +tblCasinos.casinoid AS CasinoID,tblTypes.typename AS pokerdesc from tblCasinos,tbltypes where ',' + tblCasinos.ctype + ',' like '%,' + CStr(tblTypes.typeid) + ',%' UNION select CStr(tblVideoPoker.videoid) + '|' +tblCasinos.casinoid AS CasinoID,tblVideoPoker.videodesc AS pokerdesc from tblCasinos,tblvideopoker where ',' + tblCasinos.videopoker + ',' like '%,' + CStr(tblVideopoker.videoid) + ',%' --- As the query. And this is the CFINDEX and Search statements: --- HOwever, the FindCasino is empty when I perform the above sequence of operations. The Query,GetCasinos,Correctly (From what I can gather) returns 10,052 records. Why isn't Verity properly indexing the query? The Keys are now all unique. Where can I see what Verity is doing, what errors are being thrown...how the heck can I figure out WHY the CFINDEX tag isn't working?? ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190166 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Verity Pickle...
I realise no one is following this thread anymore, but I'm just updating it in case it helps someone from the archives later. Course if anyone has any suggestions, feel free to chime in. I have changed the Select strings to make all the keys unique. I still don't understand why this is necessary, but I was advised to do so (thanks Phil) and it worked...to a point. The records are now being indexed. Until I add a fifth table. Now the select statement is fine, if I run an index on this table by itself it works. But once I add it as the fifth table in the Union query, the CFINDEX results in a 0 length collection. So the scenario is this. Union on 4 tables...CFINDEX works. Union on 5 tables (Any table,and all the SQL is valid, the QUery runs and works), and Verity fails to INdex the query. I am not dealing with a lot of records here. The Query still returns the correct number of rows, but it is CFINDEX that seems to be bombing for unknown reasons. I cannot get around this problem, and to me it seems to be a bug rather than somethign I am doing incorrect. Someone feel free to correct this if I'm wrong. As the only possible workaround, I am going to create a seperate collection for every joined One to Many table pair I have, and then run the CFSearch on these multiple collections. Messy. I've been greatly disheartened by the lack of documentation, online help, and it would appear,'buggyness' of the Verity implementation thus far. Again, if anyone has any suggestions or reasons why this is all happening, please let me know :) Here's an example of what I changed all the Select statements to: select tblCasinos.casinoid + '|' + 'H' + '|' + CStr(tblVideoPoker.videoid) AS CasinoID,tblVideoPoker.videodesc AS pokerdesc from tblCasinos,tblvideopoker where ',' + tblCasinos.videopoker + ',' like '%,' + CStr(tblVideopoker.videoid) + ',%' The letter 'H' was necessary to ensure that the keys returned would be unique.Though I still don't understand why this is necessary :) ~| Now there’s a better way to fax. eFax makes it possible to use your existing email account to send and receive faxes. Try eFax free. http://www.houseoffusion.com/banners/view.cfm?bannerid=63 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190427 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Verity Pickle...
The unique keys in a verity collection are for the same reasion you need a unique key in a database - for updates. When verity refreshes a collection, it doesn't drop the whole thing and rebuild it. It looks for the key and if it exists, it updates the record. As to why it fails with a fifth table, I'm not sure. Are you sure that when you add the fifth table you still end up with all unique keys? Have you tried making your mongo query as a view in the database, and then calling on CF query on the view and indexing that? ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190438 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
