Databases
StackOverflow2013
Postsdbo
POAggregate table before join MySQL (GROUP BY)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAggregate table before join MySQL (GROUP BY)
    primarykey
    data
    text
    <p>I need to agregate a table before joining it with other tables.</p> <pre><code>wp_postmeta GROUP BY wp_postmeta.post_id </code></pre> <p>Is it possible? Where should I put it in? Here is my code:</p> <pre><code>SELECT wp_posts.post_content, wp_posts.ID, wp_terms.slug FROM wp_posts JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id JOIN wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id WHERE wp_posts.post_type = 'my-type' AND wp_posts.post_status = 'publish' AND wp_terms.slug IN ('field1', 'field2', 'field3') AND ( wp_postmeta.meta_key = 'exclude' AND wp_postmeta.meta_value &lt;&gt; '111' OR wp_postmeta.meta_key = 'include' AND wp_postmeta.meta_value = '22' OR wp_postmeta.meta_key &lt;&gt; 'include' AND wp_postmeta.meta_key &lt;&gt; 'exclude' ) </code></pre> <p>Found a new way of doing it while testing ruakh example. I don't know why it works, but it does:</p> <pre><code>SELECT wp_posts.post_content, wp_posts.ID, wp_terms.slug FROM wp_posts JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id JOIN wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id WHERE wp_posts.post_type = 'my-type' AND wp_posts.post_status = 'publish' AND wp_terms.slug IN ('field1', 'field2', 'field3') AND NOT wp_posts.id IN # ADDED NOT ( SELECT wp_postmeta.post_id FROM wp_postmeta WHERE wp_postmeta.meta_key = 'exclude' AND wp_postmeta.meta_value = '111' #SINCE NOT ABOVE I CHANGED &lt;&gt; to = OR wp_postmeta.meta_key = 'include' AND wp_postmeta.meta_value &lt;&gt; '22' #SINCE NOT ABOVE I CHANGED = to &lt;&gt; #I DELETED THIS LINE ) </code></pre> <p>Now the query gives back data if include or exclude isn't set. And if they are it checks the ID.</p> <p>Any comments on my change?</p> <p>MORE IMPROVEMENTS:</p> <pre><code>SELECT wp_posts.post_content, wp_posts.ID, wp_terms.slug FROM wp_posts JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id JOIN wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id WHERE wp_posts.post_type = 'my-type' AND wp_posts.post_status = 'publish' AND wp_terms.slug IN ('field1', 'field2', 'field3') AND wp_posts.id NOT IN ( SELECT wp_postmeta.post_id FROM wp_postmeta WHERE wp_postmeta.meta_key = 'exclude' AND wp_postmeta.meta_value IN ('$id', '$id,%', '%,$id,%', '%,$id') OR wp_postmeta.meta_key = 'include' AND wp_postmeta.meta_value NOT IN ('$id', '$id,%', '%,$id,%', '%,$id') </code></pre> <p>Example of what the wp_postmeta table might look like:</p> <p><strong>Senario 1 - Only pages with id 18 shall get the data:</strong></p> <pre><code>meta_id post_id meta_key meta_value 1 30 include 18 2 30 _edit_lock 1322225789:1 3 30 _edit_last 1 </code></pre> <p><strong>Senario 2 -Pages with id 18 shall not get the data:</strong> </p> <pre><code>meta_id post_id meta_key meta_value 1 30 exclude 18 2 30 _edit_lock 1322225789:1 3 30 _edit_last 1 </code></pre> <p><strong>Senario 1 - All pages shall get the data:</strong></p> <pre><code>meta_id post_id meta_key meta_value 2 30 _edit_lock 1322225789:1 3 30 _edit_last 1 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USHakan
    1. USHakan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAggregate table before join MySQL (GROUP BY)
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload