CODEWITHSUNDEEP
mysql
santa
santa

Reputation: 12512

Combine two queries into one and reorder

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:

What I would like to see:

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.

Upvotes: 0

Views: 300

Answers (5)

Javier Jimenez Matilla
Javier Jimenez Matilla

Reputation: 312

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 :)

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35563

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.

Upvotes: 7

Asav Vora
Asav Vora

Reputation: 61

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.

Upvotes: 0

Horaciux
Horaciux

Reputation: 6477

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

Upvotes: 0

simon.ro
simon.ro

Reputation: 3302

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.

Upvotes: 0

Related Questions