I have two queries that work fine by themselves, however, because the second one is ran within the loop of the first one, I can not reorder results returned by the first query.
The first query always returns results, the second query - sometimes, but when it does I need those results to be on top. By default the results are sorted by distance, starting with the closest.
For example, here's what I get now:
- Name1 (phone1) - 0.1 mi
- Name2 (phone2) - 0.4 mi
- Name3 (phone3) - 1.3 mi <- Now open (query 2 match)
- Name4 (phone4) - 2.4 mi
What I would like to see:
- Name3 (phone3) - 1.3 mi <- Now open (query 2 match)
- Name1 (phone1) - 0.1 mi
- Name2 (phone2) - 0.4 mi
- Name4 (phone4) - 2.4 mi
Here are my current queries (simplified):
Query 1:
SELECT t1.busName, t1.busPhone FROM t1 WHERE t1.lat BETWEEN $min_lat AND $max_lat AND t1.lon BETWEEN $min_lon AND $max_lon ORDER BY (POW((t1.lon-$lon),2) + POW((t1.lat-$lat),2)) LIMIT 5 Query 2:
SELECT COUNT(t3.rule_id) AS rcount FROM t3 LEFT JOIN t2 ON (t3.rule_busID = t2.busID) WHERE t3.APIid = '".$APIid."' Another problem that I have is that there's no way to establish a direct connection between t1 and t3. The only way is to have t2
t1.APIid = t2.APIid t2.busID = t3.rule_busID Table structure as follows:
t1 -------------------------------------- busName | busPhone | lon | lat | APIid t2 -------------------------------------- busID | APIid t3 -------------------------------------- rule_id | rule_busID Currently, with two queries, if I return 10 results I have to run 11 queries. Ideally I'd like to do it just once.
Sorry, this might be quite obvious, but I'm stuck.
5 Answers
Answers 1
Assumption: t3 does not contain the column APIid ("t3.APIid" in query 2 in question should read "t2.APIid").
Because you are ordering by columns not included in the select clause you need to perform the count calculation either as a derived table, or as a correlated subquery.
Derived Table
Here you perform the COUNT() & GROUP BY within a subquery and that result joined to the main query.
SELECT t1.busName , t1.busPhone , COALESCE(r.rcount,0) rcount FROM t1 LEFT JOIN ( SELECT t2.APIid , COUNT(t3.rule_id) AS rcount FROM t3 INNER JOIN t2 ON t3.rule_busID = t2.busID GROUP BY t2.APIid ) r ON t1.APIid = r.APIid WHERE t1.lat BETWEEN $min_lat AND $max_lat AND t1.lon BETWEEN $min_lon AND $max_lon ORDER BY (POW((t1.lon - $lon), 2) + POW((t1.lat - $lat), 2)) #LIMIT 5 ;
Correlated Subquery
An alternative approach is to perform the count calculation inside the select clause of the main query. This style of subquery can cause performance issues, but if the number of rows being returned from the main query isn't large then this approach may perform adequately well.
SELECT t1.busName , t1.busPhone , COALESCE(SELECT COUNT(t3.rule_id) FROM t3 INNER JOIN t2 ON t3.rule_busID = t2.busID WHERE t2.APIid = t1.APIid ),0) as rCount FROM t1 WHERE t1.lat BETWEEN $min_lat AND $max_lat AND t1.lon BETWEEN $min_lon AND $max_lon ORDER BY (POW((t1.lon - $lon), 2) + POW((t1.lat - $lat), 2)) #LIMIT 5 ;
Note: In either approach there is no value in using a LEFT JOIN from t3 on t2. If t3 has rules that don't link to t2, it also becomes impossible to link those rules to t1. So, just use an INNER JOIN between t3 and t2.
You may require the use of COALESCE() or IFNULL() to return zero if there is no matching count. You can use either function but I prefer the ANSI standard COALESCE()
Adjust the LIMIT to suit your need.
Answers 2
Not sure if that really works, but you could try the following approach: Use your Query1 as subquery in the FROM-clause (to add the inner sorting and limit), select the rule-count in the SELECT-clause. ORDER the result by count DESC.
Haven't tested it, but that should look something like:
SELECT sub1.busName, sub1.busPhone, (SELECT COUNT(t3.rule_id) AS rcount FROM t3 LEFT JOIN t2 ON (t3.rule_busID = t2.busID) WHERE t2.APIid = t1.APIid) as rCount FROM ( SELECT t1.busName, t1.busPhone FROM t1 WHERE t1.lat BETWEEN $min_lat AND $max_lat AND t1.lon BETWEEN $min_lon AND $max_lon ORDER BY (POW((t1.lon-$lon),2) + POW((t1.lat-$lat),2)) ASC LIMIT 5 ) as sub1 ORDER BY rCount DESC But actually I wouldn't to that, I'd probably stick with your current approach of individual less complex queries and to the re-ordering afterwards in the application.
Answers 3
you could use this approach:
select name, phone from ( select 0 order, name, phone from ... union all select 1, name, phone from ... )q order by q.order Answers 4
You can directly provide the join if you have Relational Database(i.e connection between your each tables including direct or indirect).
so your query simplified into one like below:
SELECT t1.busName, t1.busPhone, COUNT(t3.rule_id) AS rcount FROM t1 INNER JOIN t2 on t2.APIid=t1.APIid LEFT JOIN t3 on t2.busID=t3.rule_busID WHERE t1.lat BETWEEN $min_lat AND $max_lat AND t1.lon BETWEEN $min_lon AND $max_lon AND t3.APIid = '".$APIid."' ORDER BY (POW((t1.lon-$lon),2) + POW((t1.lat-$lat),2)) LIMIT 5
use LIMIT keyword only when you restrict your data to be display.
Answers 5
select name, phone from ( select ROW_NUMBER() over (order by name, phone) as order, name, phone from ... union all select 1, name, phone from ... )q order by q.order
use ROW_NUMBER() over (order by name, phone) you can partition and order asc and descending for generating and identity simulation and then you can union join or do what you want. (you can do the second select in the field of the first query select a, (select b from c where d.a = c.a) from d
I don't understand the output what you expect.
Sorry for my English :)
0 comments:
Post a Comment