CODEWITHSUNDEEP
mysqlexpressionenginecategories
BellamyStudio
BellamyStudio

Reputation: 771

Expressionengine mysql help needed! - get entry from category A & (B OR C)

I've been struggling with this for a couple of days and I'm getting nothing back from the EE forums. Essentially I'm trying to do this (see the category bit):

{exp:channel:entries channel="events" category="7&(175|177)"}

but you can't mix AND/OR category requests with native tags out of the box

I've been trying to write a custom query to handle it but I'm stuck on how to handle the category posts table.

Currently my query looks like this but the category query doesn't work.

{exp:query sql="SELECT 
             type.cat_id, 
             type.cat_name as cat_name, 
             type.cat_url_title,
             type.group_id,
             t.title as title,
             t.status,
             t.channel_id,
             t.site_id,
             d.field_id_32 as date,
             p.cat_id as cat_id
            FROM 
             exp_categories type JOIN 
             exp_category_posts p ON type.cat_id = p.cat_id JOIN
             exp_channel_titles t ON p.entry_id = t.entry_id JOIN
             exp_channel_data d ON t.entry_id = d.entry_id
            WHERE 
             t.status = 'open' AND
             t.site_id = 1 AND
             type.group_id = 2 AND
             /* problem bit! */
             (p.cat_id = 7 AND ((p.cat_id = 175) OR (p.cat_id = 177))) AND
             t.channel_id = 3
            GROUP BY 
             type.cat_id
            ORDER BY
             type.cat_order
  LIMIT 5"}

If anyone has done something like this before or can see where I'm going wrong I'm really appreciate a steer!

Upvotes: 0

Views: 1490

Answers (4)

AllInOne
AllInOne

Reputation: 1450

@Penzizzle I think the secret here is that you need to basically examine the three categories you are interested in each in their own column.

Here is a simplified version of your query that demonstrates this technique:

SELECT 
   t.entry_id,
   cat1.cat_id as cat1,
   cat2.cat_id as cat2,
   cat3.cat_id as cat3
FROM
   exp_weblog_titles t
JOIN
   exp_category_posts as cat1 ON cat1.entry_id = t.entry_id
JOIN
   exp_category_posts as cat2 ON cat2.entry_id = t.entry_id
JOIN
   exp_category_posts as cat3 ON cat3.entry_id = t.entry_id
WHERE
   cat1.cat_id = 175 AND (cat2.cat_id = 177 OR cat3.cat_id = 175)

See what that does for you?

Upvotes: 1

Derek Hogue
Derek Hogue

Reputation: 4564

This can't be accomplished in a single query. I'd build a simple plugin for this (get started with pkg.io) and do a couple of queries, returning entry_ids to be passed to a Channel Entries tag.

$cat_7_entries = $final_entries = array();
$cat_7_query = $this->EE->db->query("SELECT entry_id FROM exp_category_posts WHERE cat_id = 7");
if($cat_7_query->num_rows() > 0)
{
    foreach($cat_7_query->result_array() as $row)
    {
        $cat_7_entries[] = $row['entry_id'];
    }
    $cat_7_entries = implode(',', $cat_7_entries);

    $entries_query = $this->EE->db->query("SELECT entry_id FROM exp_category_posts WHERE cat_id IN(175,177) AND entry_id IN(".$cat_7_entries.")");
    if($entries_query->num_rows() > 0)
    {
        foreach($entries_query->result_array() as $row)
        {
            $final_entries[] = $row['entry_id'];
        }
        $vars = array(0 => array('entry_ids' => implode('|', $final_entries)));
        return $this->EE->TMPL->parse_variables($this->EE->TMPL->tagdata, $vars);
    }
}

Then just wrap a standard Channel Entries tag with your plugin tag, using parse="inward".

{exp:my_plugin:my_method parse="inward"}
{exp:channel:entries channel="events" entry_id="{entry_ids}"}
...
{/exp:channel:entries}
{/exp:my_plugin:my_method}

The beauty with this is that the standard Channel Entries tag does all the checking of channel, status, expiration date, etc, etc for you, but will only return entries that match all that AND your specified list of qualifying entry_ids.

Upvotes: 1

Scott Hepler
Scott Hepler

Reputation: 421

Wow, what a query! How about taking a little step back? Why not make your outer loop just part of the logic, then do the balance inside the loop:

{exp:channel:entries channel="events" category="7"}
  {categories show="175|177"}
    {!-- do stuff --}
  {/categories}
{/exp:channel:entries}

That might be just what you need for a nice Friday ;)

-s.

Upvotes: 1

duffy356
duffy356

Reputation: 3716

try this where clause:

WHERE 
  t.status = 'open' AND
  t.site_id = 1 AND
  type.group_id = 2 AND
  t.channel_id = 3 AND
  p.cat_id = 7 OR p.cat_id = 175 OR p.cat_id = 177

if you really want this logic:

/* problem bit! */
(p.cat_id = 7 AND ((p.cat_id = 175) OR (p.cat_id = 177))) AND

then you might have to reorganize your table and logic.

Upvotes: 0

Related Questions