Calling categories with query module

#1 / Aug 24, 2007 7:43pm

monickels

4 posts

So I’ve got this code that calls up entries from two weblogs that meet certain criteria and displays them.

{exp:query limit="20" sql="SELECT t.weblog_id, d.weblog_id, t.status, t.url_title, d.field_id_26, d.field_id_14, d.field_id_22, t.title, t.entry_date FROM exp_weblog_titles AS t LEFT JOIN exp_weblog_data AS d ON t.entry_id = d.entry_id WHERE (t.weblog_id='1' AND t.status='Used') OR (t.weblog_id='2' AND t.status='open') ORDER BY t.entry_date DESC"}

Now I want to also call up the categories for that entry. Is the best way to do this with some kind of join in the same query? Or should I be able to nest a query? I’ve tried this query nested inside the other but no dice.

{exp:query sql="SELECT c.cat_id, c.cat_name, p.entry_id FROM exp_categories c, exp_category_posts p WHERE p.entry_id = {entry_id} ORDER BY cat_name LIMIT 10"} {/exp:query}

I don’t, in fact, really know what I’m doing here, but I’m trying to avoid going all the back out to straight PHP.

#2 / Aug 25, 2007 10:50am
monickels
4 posts
Let me take a different approach to this.

What I’m trying to do, overall, is to include entries from two weblogs that have certain statuses but to exclude others. This is easy enough to do with normal EE tags, but the problem comes when I want to have a set number of entries appear on each page.

For example, if I use EE tags and limit the entries to the most recent 20, but 6 of the most recent do not meet my criteria, then all I’ll get is 14 entries on the page. Here’s an example of the code, where I use the presence or absence of custom fields to determine which weblog an entry is pulled from:
```
{exp:weblog:entries weblog="doubletongued|citations" orderby="date" sort="desc" limit="20" status="Open|Used|Closed|McGH|Nonce" disable="member_data|trackbacks" offset="10"}

{if pos != ""}weblog 1 entry data{/if}

{if status != "Used" && citepos != ""}weblog 2 entry data{/if}

{/exp:weblog:entries}
```
Of, course, as I said, with this method, there’s no way to guarantee that I’ll always see 20 entries per page, since the 20 applies to *all* the recent entries and I can’t put the necesary limiting conditions on the exp:weblog:entries tags.

So that means that I have to use some kind of SQL query, like so:
```
{exp:query limit="20" sql="SELECT t.weblog_id, d.weblog_id, t.status, t.url_title, d.field_id_26, d.field_id_14, d.field_id_22, t.title, t.entry_date FROM exp_weblog_titles AS t LEFT JOIN exp_weblog_data AS d ON t.entry_id = d.entry_id WHERE (t.weblog_id='1' AND t.status='Used') OR (t.weblog_id='2' AND t.status='open') ORDER BY t.entry_date DESC"}

{if weblog_id == "2" AND status == "open"}weblog 2 entry

{if:elseif weblog_id == "1" AND status == "Used"}weblog 1 entry
{/if}

{paginate}
Page {current_page} of {total_pages} pages {pagination_links}
{/paginate}

{/exp:query}
```
This works for basica tags that can be found in exp_weblog_data. But the problem here is that I want to get the full range of tags out of each entry, like categories, which are stored in other tables, and I’m not sure how to go about getting them. It’s going to either have to involve complex joins or multiple queries. I don’t know enough about joins and nested queries seem not to work for me.

So, with a lot of searching of the forums, I came up with some code that Paul offered up to someone else who was having a similar problem. My version looks like this:
```
<?php
$entries = array();
$query = $DB->query("SELECT t.entry_id, d.entry_id, t.weblog_id, d.weblog_id, t.status, t.url_title, d.field_id_26, d.field_id_14, d.field_id_22, t.title, t.entry_date FROM exp_weblog_titles AS t LEFT JOIN exp_weblog_data AS d ON t.entry_id = d.entry_id WHERE (t.weblog_id='1' AND t.status='Used') OR (t.weblog_id='2' AND t.status='open') ORDER BY t.entry_date DESC");

if ($query->num_rows > 0)
{
    foreach($query->result as $row)
    {
        $entries[] = $row['entry_id'];
    }
}
$ids = implode('|', $entries);

?>
{exp:weblog:entries entry_id="<?php echo $ids; ?>"}

{entry_id}

{if weblog_id == '1'}weblog 1 data{/if}

{if weblog_id == '2'}weblog 2 dat{/if}

{/exp:weblog:entries}
```
But that doesn’t seem to work at all. I get no results. (Globals DB and TYPE are already called higher up on the page.)

So, does this make any sense?

#3 / Aug 25, 2007 3:52pm

monickels

4 posts

Guess I’m asking myself questions. I ended up using straight PHP. Of course, the problem with this is that I don’t get pagination and other goodies that come with the query module. Can anyone see a good way to do this in the query module?

<?php
{global $DB, $TYPE;
}
$query = $DB->query("SELECT exp_weblog_titles.entry_id, exp_weblog_titles.weblog_id, exp_weblog_titles.title, exp_weblog_titles.status, exp_weblog_titles.entry_date, exp_weblog_titles.comment_total FROM exp_weblog_titles LEFT JOIN exp_weblogs ON exp_weblog_titles.weblog_id = exp_weblogs.weblog_id WHERE (exp_weblog_titles.weblog_id='1' AND exp_weblog_titles.status='Used') OR (exp_weblog_titles.weblog_id='2' AND exp_weblog_titles.status='open') ORDER BY entry_date desc LIMIT 20");

    foreach($query->result as $row)
    {
        $entry = $row['entry_id'];
        echo("$entry 
");
        $query = $DB->query("SELECT exp_categories.cat_id, exp_category_posts.entry_id, exp_categories.cat_name 
        FROM exp_category_posts 
LEFT JOIN exp_categories ON exp_category_posts.cat_id=exp_categories.cat_id
WHERE exp_category_posts.entry_id='$entry'");

    foreach($query->result as $row)
    {
        $catname = $row['cat_name'];
        echo("$catname
");
   
    }
    }
   
?>

#4 / Aug 27, 2007 12:47pm

Robin Sowell
13255 posts

You’d want to do this with a join- that’s going to be pretty resource intensive, as that cat query is going to run for each entry returned. I’d have to think on it to come up with the query for you, but there’s an extension that lets you limit by a custom field. That might work for what you’re going for.

Thread

monickels

monickels

monickels

Robin Sowell

Username

Password

Thread