Expression Engine SQL Query Entries List by Authors

expression engine sql query entries list by authors

Mark - here's a repost of the answer I posted on the other dupe question:

---

The best approach here, since you need your custom fields parsed, is to first find the entry_ids of the latest 4 entries from distinct authors, and then pass those to the channel:entries tag through an embed using the entry_id parameter.

This should work (be sure to replace the channel_id with the appropriate integer). Replace your entire current chunk of code with this:

{embed="embeds/_latest_per_member" entry_ids="{exp:query sql="SELECT entry_id, author_id FROM exp_channel_titles WHERE entry_date IN( SELECT MAX(entry_date) FROM exp_channel_titles  WHERE status != 'closed' AND channel_id = 1 GROUP BY author_id ) ORDER BY entry_date DESC LIMIT 4" backspace="1"}{entry_id}|{/exp:query}"}

Then your embeds/_latest_per_member template can look something like this:

{exp:channel:entries channel="channel_name" entry_id="{embed:entry_ids}"}
    {author_id}<br />
    <a href="{path=portfolios/gallery/{username}}"><img src="{thumbnail}"></a><br>
    <a href="{path=portfolios/gallery/{username}}">{title}</a><br />
{/exp:channel:entries}

You had mentioned that this code gave you a recursive error - that means that you've put another call to the embed within the embed. Don't do that.

expression engine sql query limit entries

This should do the trick. Be sure to replace t.channel_id = 1 with the actual channel_id of your portfolios channel.

What this will do is list all of the members who have posted entries in that channel. Note that because we're doing an SQL query, {avatar_url} isn't prepared, so we just cheat and hardcode the path to the avatars folder.

{exp:query sql="
    SELECT m.member_id, m.username, m.screen_name, m.avatar_filename
    FROM exp_members m
    LEFT JOIN exp_channel_titles t
    ON t.author_id = m.member_id
    WHERE t.channel_id = 1
    GROUP BY m.member_id
    ORDER BY m.screen_name ASC
"}
        <a href="{path=portfolios/gallery/{username}}"><img src="/images/avatars/{avatar_filename}" /></a><br>
        <a href="{path=portfolios/gallery/{username}}">{screen_name}</a><br>
{/exp:query}

Showing a tag cloud by author

Using Tagger and the Query Module you should be able to get what you need (probably Solspace Tags, too, I just haven't used that one so can't say for sure).

A query like this

SELECT tag_name  
FROM exp_tagger
WHERE author_id = 7

Gives a set of tags for that author.

You can use it with the Query Module like this, using the current entry's author_id as a dynamic part of the query.

{exp:query sql= "SELECT tag_name FROM exp_tagger WHERE author_id = {author_id}"}
    {tag_name}
{/exp:query}

Author Dropdown Select Field in a SafeCracker Form

Use the query module maybe?

<select name="author">
{exp:query sql="SELECT member_id, screen_name
    FROM exp_members
    WHERE group_id = X;"
}
    <option value="{member_id}">{screen_name}</option>
{/exp:query}

Expression Engine: split entries into groups

You can try using the Modulo Operator plugin to acheive this with any number of entries. Something like this:

{if count == "1"}
    <div class="entry_group">
{/if}
{if '{exp:modulo dividend="{count}" divisor="4"}' == 0}
    </div>
    <div class="entry_group">
{/if}
        <div class="entry" id="{count}"><span>{title}</span></div>
{if count == total_results}
    </div>
{/if}

The plugin is for EE1 only, but converting a plugin from EE1 to EE2 is a breeze.

Expression engine: Maximum number of entries allowed per membrr group

Yes, but it would require writing an extension. The logic would be something like this (assuming you're talking about limiting on the back-end ... from the front-end, if you're using a Safecracker entry form for example, you'd need to take a different approach):

1. use the sessions_end hook
2. check to make sure you're in the control panel ($this->EE->input->get('D') == 'cp')
3. check to make sure you're on the publish screen ($this->EE->input->get('C') ==
'content_publish')
4. query the database to see how many entries in exp_channel_titles with the channel_id of $this->EE->input->get('channel_id') belong to $this->EE->session->userdata('member_id')
5. if the result is greater than your allowed maximum, show them an error

That should get you started.

---

Related Topics