[SOLVED] Intense joined query question

[Chuck36963]

Hi all,

I've been working on a listing problem and I can't figure out how to work it out. I have looked far and wide on the web to find answers, but I'd like other peoples input on my project in the whole. I really need MySQL wizz to give me a hand (and maybe refer me to books to get me to the wizz level myself).

First off, english is a second language to me and sometimes my sentences might be a little awkward. Please forgive me. Mon français est bien meilleur.

[b]Description of database[/b]
My database is pretty large and I tried to design it following the best practice (using a lot of many-to-many relationship tables). It holds the description of over 200 stores, with opening hours, services, locations, etc. I am trying to create a AJAX store search engin, with Google Map display, where the user can choose from a number of fields the conditions of his search. When he validates his search, the query is sent to the server, which retrieves the list and update the map.

The conditions are:
[list]
[*]Postal Code
[*]City id
[*]Service id
[*]Opening hours
[/list]

Next is the schematic description of the table store_info
| idStore | ... | idCity | openingHourMonday | closingHourMonday | openingHourTuesday | closingHourTuesday | ... |

The schematic description of the table store_service
| idService | vchLabel | ... |

The schematic description of the table store_info_service
| idService | idStore |

The schematic description of the table store_info_postalcode
| vchPostalCode | idStore |

(NOTE: those Postal Codes are based on the flyer distribution list, so it doesn't contain all the postal code of the country, and some user input can return NULL).


[b]the Script[/b]
Getting the information out with JOINED query is not a problem, the trouble is getting the right data out. Getting the conditions expressions right is what I need.

So, I've built my PHP script to compose my query in parts ($SELECT, $FROM, $WHERE). Based on the GET parameters, I add to the query string the needed expressions to refine the query. I unite the parts in $query, and run it. Its been working OK, but not up to my linking. I wan't it bullet proof.

Here is the base query:

[tt]SELECT i.*,
v.`vchLabel` AS ville,
FROM `stores_info` AS i
JOIN `geo_ville` AS v ON i.`idVille` = v.`idVille`
WHERE i.`swActiv`=1
GROUP BY i.`idStore`;[/tt]

[b]Here are some of my solutions:[/b]

[b]1. Postal code:[/b] If a user input a postal code which returns no data, needs to be repeated with a substring of the P.C. (eg.H1H1H1 -> H1H1H_). Using LIKE in

[tt]SELECT i.*,
v.`vchLabel` AS ville,
FROM `stores_info` AS i
JOIN `geo_ville` AS v ON i.`idVille` = v.`idVille`
JOIN `stores_cp` AS cp ON i.`idStore` = cp.`idStore`
WHERE i.`swActiv`=1
AND cp.`cp` LIKE 'H1H1H1'
GROUP BY i.`idStore`;[/tt]

return 0

[tt]SELECT i.*,
v.`vchLabel` AS ville,
p.`vchLabel` AS province
FROM `stores_info` AS i
JOIN `geo_ville` AS v ON i.`idVille` = v.`idVille`
JOIN `stores_cp` AS cp ON i.`idStore` = cp.`idStore`
WHERE i.`swActiv`=1
AND cp.`cp` LIKE 'H1H1H_'
GROUP BY i.`idStore`;[/tt]

return 1 store


[b]2. Closing time:[/b] the user can ask for stores open now and for the next 30, 60, or 300 minutes (5hre). It could be any number of minutes, but if it goes later then midnight, the closing time is removed and the user receives a warning.

[tt]SELECT i.*,
v.`vchLabel` AS ville,
FROM `stores_info` AS i
JOIN `geo_ville` AS v ON i.`idVille` = v.`idVille`
WHERE i.`swActiv`=1
AND '13:20:05' BETWEEN i.`wednesdayopen` AND i.`wednesdayclose`
AND '18:20:05' < i.`wednesdayclose`
GROUP BY i.`idStore`;[/tt]

[b]3. idCity:[/b] the user can choose multiple cities from a drop-down list. The id get passed to the request in the form of a list separated by coma. I then use the IN() statement

[tt]SELECT i.*,
v.`vchLabel` AS ville,
FROM `stores_info` AS i
JOIN `geo_ville` AS v ON i.`idVille` = v.`idVille`
WHERE i.`swActiv`=1
AND i.`idVille` IN (401,1102)
GROUP BY i.`idStore`;[/tt]

Returns the list of all the stores located in city 401 or 1102.

And thats where it gets complicated.

[b]Trouble #1: The services[/b]
The user has checkbox associated with the idService. The idServices get sent to the request in the same form as the idCity (eg. 1,4).

At first I used the IN() statement, but realised that it got the store that offers service 1 OR 4. I needed the stores that offered the two services.

So I used = to compare the string to a subquery (SELECT GROUP_CONCAT(idServices) FROM store_info_service WHERE idStore = $idStore), but of course a store that offers services 1,2,3,4 was not returned, but it DID offer the services requested. I tried the IN() construct with this subquery, but nothing conclusive came out.

[b]Trouble #2: Concurrent search clauses[/b]
The user can select more then one condition for his search. For exemple, he could look for the stores open in the next hour, around his own postal code (eg. H1H___).

It has been a requisite of this project that the user receives listing whatever the cost. If one condition is not met, it is bypassed to give the stores corresponding to the other conditions. A javascript alert informs the user of that.

How do I structure such a script? I've made it work with TIME and POSTAL CODE, doing repeated queries until a result is found.

[tt]while (no result){
     if (isset(T)){
          unset(T);
          try again;
     }else{
          reset(T);
     }

     if (isset(PC)){
          truncate PC;
          try again;
     }
}
[/tt]
Of course, this is a simplified version of my PHP script. That way, I try with the T, without the T, reducing PC every 2 queries. It seems pretty hard and time consumming right now, imagine when I add CITY and SERVICE to the equations.

Does anyone have an idea how I could simplify this process?

One hypothesis was to run the 4 queries separately and cross-referenced the results in PHP, only keeping the result sets which have common results, and keeping a warning on the defective queries. If a query is really messed up, I could keep only one condition and return these results, based on semantic priority (Postal code > City > Opening hours > Services).

As you can see this is a though one. I'd really like to be able to wrap my mind around problems of the kind and come up with effective solutions. I tried to find books only about queries (not administration). If you have any recommandation, go ahead.

Thanks.

[artacus]

Ok, I've got a short attention span and you've exceeded the max execution time :)

But when I run into problems like this I'll often just do one query but "score" the results.
So for all queries involving zip codes, I'd probably just search on the first 3 characters so I'm pretty much guaranteed results. Then I'll use a CASE statement to score the results so if it matches all characters of the zip code it will have a high score if it only matches the first 3 characters it will have a low score or 0.

[btherl]

For Trouble #1, how about using group_concat() and string comparison functions?  It doesn't seem hugely efficient though, as group_concat() must be called on every matching store before doing the test.

A better alternative might be to select all stores with service 1 in a subquery, and all stores with service 2 in another subquery, then do an inner join on idStore.  An inner join is the same as intersection/and instead of union/or, so it is exactly what you want.  Since you're dynamically generating your queries, you can generate as many subqueries as necessary.

As for trouble 2, I would go with the many queries followed by php post-processing approach.  It's just too messy to put such complex rules (and rules that match human thinking will always be messy and complex) into SQL.

[Chuck36963]

Thanks for your responses.

[quote author=artacus link=topic=119426.msg489124#msg489124 date=1166643944]
Then I'll use a CASE statement to score the results so if it matches all characters of the zip code it will have a high score if it only matches the first 3 characters it will have a low score or 0.
[/quote]

Good suggestion, I'll give it a try.

[quote author=btherl link=topic=119426.msg489377#msg489377 date=1166672664]
A better alternative might be to select all stores with service 1 in a subquery, and all stores with service 2 in another subquery, then do an inner join on idStore.  An inner join is the same as intersection/and instead of union/or, so it is exactly what you want.[/quote]

Someone on an other forum ([url=http://www.dbforums.com/showthread.php?p=6248972]http://www.dbforums.com/showthread.php?p=6248972[/url]) suggested me a subquery, I tried it, but did not work as expected:

[tt]SELECT i.*,
v.`vchLabel` AS ville,
FROM `stores_info` AS i
JOIN `geo_ville` AS v ON i.`idVille` = v.`idVille`
JOIN ( [color=red]select idStore from stores_service_idStore where idService in ( 4,5 ) group by idStore having count(*) = 2[/color] ) as goodstores on goodstores.idStore = i.idStore
WHERE i.`swActiv`=1
AND i.`idVille` IN (401)[/tt]

It gave me 1 store (A). I then tried to run it again, but this time with only 1 idService (5). To my surprise, it gave me 1 OTHER store (B). Of course, store A should have been outputed also, since it also has idService 5.

I "did not trusted" my results so I ran the subqueries alone.
The query with 1 service contained all the results of the query with 2 services, as expected.

Your impressions?

[btherl]

Ooh, that subquery looks good.  Very clever.  And the join with the subquery looks fine to me.

I would guess that it's either one of the other joins or one of your conditions.  How about removing each of the other joins and conditions one by one until you find which one is excluding the stores that you expect should be in the results?  Or you can start with the subquery and add conditions and joins until one of them doesn't act how you expect.

[Chuck36963]

Hi,

The subquery cited earlier do work like a charm. There was an corruption in my database (duplicated data). I hope this will help someone someday.