def item_list(req, pager, perex=False, **kwargs):
perex = ", perex, format " if perex else ""
join = ""
public = kwargs.pop("public", False)
tag = kwargs.pop("tag", None)
if tag:
kwargs["t.name"] = tag
join = """
JOIN articles_tags at ON (at.article_id = a.article_id)
JOIN tags t ON (t.tag_id = at.tag_id)
"""
keys = list("%s %s %%s" % (k, "in" if islistable(v) else "=") for k, v in kwargs.items())
if public: # public is alias key
keys.append("public_date > 0")
keys.append("state != 0")
cond = "WHERE " + " AND ".join(keys) if keys else ""
tran = req.db.transaction(req.log_info)
c = tran.cursor(DictCursor)
c.execute(
"""
SELECT a.article_id, serial_id, author_id, email, l.name AS author,
create_date, public_date, title, uri, locale, state %s
FROM articles a JOIN logins l ON (a.author_id = l.login_id)
%s %s
ORDER BY %s %s LIMIT %%s, %%s
"""
% (perex, join, cond, pager.order, pager.sort),
tuple(kwargs.values()) + (pager.offset, pager.limit),
)
items = []
for row in iter(c.fetchone, None):
item = Article()
item.from_row(row)
items.append(item)
# endwhile
c.execute("SELECT count(*) FROM articles a %s %s" % (join, cond), kwargs.values())
pager.total = c.fetchone()["count(*)"]
tran.commit()
return items
