CODEWITHSUNDEEP
sql
Nathaniel
Nathaniel

Reputation: 41

explain this short SQL query

Can someone please explain me this SQL query. This shows me the popular stores, but it doesn't works well.

sql = "SELECT t.name, t.slug, tt.count 
FROM ".$wpdb->prefix."terms AS t INNER JOIN ".$wpdb->prefix."term_taxonomy AS tt ON t.term_id = tt.term_id
WHERE 
  tt.taxonomy IN ('$tax_name') 
  AND tt.count > 0 
GROUP BY tt.count DESC 
ORDER BY RAND() LIMIT $the_limit";

Upvotes: 3

Views: 217

Answers (4)

stevepastelan
stevepastelan

Reputation: 1304

For GROUP BY to work, you need to apply some kind of aggregating function to everything you're not GROUPing BY.

I assume what you want is the total count for each store:

sql = "SELECT t.name, t.slug, sum(tt.count) as count
FROM ".$wpdb->prefix."terms AS t

INNER JOIN ".$wpdb->prefix."term_taxonomy AS tt 
ON t.term_id = tt.term_id

WHERE tt.taxonomy IN ('$tax_name')
GROUP BY tt.term_id
HAVING count > 0";

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425381

I believe you want something like this:

SELECT  *
FROM    (
        SELECT  t.name, t.slug, SUM(tt.count) AS cnt
        FROM    terms t
        JOIN    term_taxonomy tt
        ON      tt.term_id = t.term_id
        WHERE   tt.taxonomy IN ($tax_name)
                AND tt.count > 0
        GROUP BY
                t.term_id
        ORDER BY
                cnt DESC
        LIMIT 100
        ) q
ORDER BY
        RAND()
LIMIT 20

This will give you 20 random tags / categories of the top 100.

Upvotes: 1

kbenson
kbenson

Reputation: 1464

Your question is fairly vague, so I'll just answer what I can about the query. Maybe something will clue you in to what your solution is, since I'm not really even sure what your problem is.

SELECT
  t.name, t.slug, tt.count
FROM ".$wpdb->prefix."terms AS t
INNER JOIN ".$wpdb->prefix."term_taxonomy AS tt
  ON t.term_id = tt.term_id
WHERE
  tt.taxonomy IN ('$tax_name')
  AND tt.count > 0
GROUP BY
  tt.count DESC
ORDER BY RAND()
LIMIT $the_limit

This joins the terms and term_taxonomy tables (with whatever install prefix was used, such as wp_ resulting in wp_terms) aliased as 't' and 'tt' elsewhere in the query.

The tables are joined such that 'term' records and 'term_taxonomy' records with the same term_id are linked.

The results are limited such that the term_taxonomy.taxonomy field is one of the values passed in the $tax_name variable.

The results are then condensed such that rows with the same term_taxonomy.count are collapsed together, ordered randomly, and only the first $the_limit entries are returned.

Upvotes: 1

JNK
JNK

Reputation: 65157

It doesn't work well because you are exposing one of the major issues with MySQL aggregation, namely that there are no restrictions built in (this has to be MySQL since other RDBMS don't even allow it to parse).

You are getting random values for t.name and t.slug because you are GROUPing by tt.count but not doing anything with those other fields.

For advice on how to fix this, you need to share some sample data and desired output.

Upvotes: 1

Related Questions