Expression Engine SQL查询帮助-作者的最新文章 [英] Expression Engine SQL Query help - latest posts by author

问题描述

可能重复:
作者的表达式引擎sql查询条目列表

Possible Duplicate:
expression engine sql query entries list by authors

我正在尝试创建一个简短的侧边栏列表，以列出频道中的最新帖子，每篇帖子均由一位独立作者撰写. 关于合并这些我认为我(某种程度上)需要独立工作的查询，是否可以提供帮助?

I’m trying to create a short sidebar list of latest posts from a channel, with each post by a separate author. Could someone help as regards merging these queries I think I have (sort of) working separately?

格式:

screen name
thumbnail
title

此查询代码创建一个列表，但使用author_id而不是屏幕名称 标题可以正常运行，但是缩略图无法加载，并且路径书写不正确.

this query code creates a list, but with author_id rather than screen name title works ok, but the thumbnail doesn’t load and the path doesn’t write properly.

{exp:query sql="SELECT t.title, t.author_id FROM exp_channel_titles t 
ORDER BY author_id ASC LIMIT 4"}
{author_id}<br />
<a href="{path=portfolios/gallery/{username}}"><img src="{thumbnail}"></a><br>
<a href="{path=portfolios/gallery/{username}}">{title}</a><br />
{/exp:query} 

此查询代码可正确创建路径和用户名，但标题仅显示为{title} 缩略图不会加载.

This query code create the path properly and the user name properly but the title is just appearing as {title} thumbnail doesn’t load.

{exp:query sql="SELECT m.member_id, m.username, m.screen_name, m.avatar_filename FROM exp_members m 
WHERE m.group_id='5' "}
{screen_name}
<a href="{path=portfolios/gallery/{username}}"><img src="{thumbnail}"></a><br>
<a href="{path=portfolios/gallery/{username}}">{title}</a><br />
 {/exp:query} 

我想我需要使用JOIN，但我不是SQL查询专家.

I presume I need to use a JOIN but I’m really not that expert with SQL queries.

thumbnail是exp_channel_fields中的一个字段-知道如何将其放入查询中吗? 图像的文件名在exp_channel_data中，但是就像这样-

thumbnail is a field in exp_channel_fields - any idea how I get this into the query? the filename for the image is in exp_channel_data but it is like this -

{filedir_1}13.jpg 

如何摆脱

{filedir_1} 

是位还是创建路径?

或者我也应该在其中使用{exp:channel:吗?

or should I be using {exp:channel: in there as well ?

推荐答案

由于需要解析自定义字段，因此此处的最佳方法是首先查找来自不同作者的最新4个条目的entry_id，然后将其传递给使用entry_id参数通过嵌入来嵌入channel:entries标记.

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.

这应该起作用(确保将channel_id替换为适当的整数):

This should work (be sure to replace the channel_id with the appropriate integer):

{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}"}

然后您的 embeds/_latest_per_member 模板可以如下所示:

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}

这篇关于Expression Engine SQL查询帮助-作者的最新文章的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！