ExpressionEngine’s SQL Query Module

Aug. 19, 2008

10:40 pm

I promised more ExpressionEngine tips, and I intend to deliver.

Today, let's take a look at EE's SQL query module. Like the gallery module, this one's only available with a paid license, and for my money, the functionality these two tools bring to the table more than make up for the $100 personal site fee.

The query module is, for lack of a better phrase, almost completely frill-free. It takes one main argument: a valid SQL query statement. It then loops through the results just like exp:weblog:entries, allowing you to access these fields by referencing a field's short name wrapped in curly braces. But where exp:weblog:entries decides what SQL to run based on the parameters you pass it, exp:sql gives you a main-line connection to your database.

True, for most everyday EE tasks, the complex, behind-the-scenes work baked into most tag pairs is a help and not a hindrance – especially if you don't know much (or any) SQL. But in certain circumstances, exp:query is indispensible.

Nesting queries.

One of EE's big limitations is you can't nest one exp:weblog:entries call in another. I recently needed to do just that for a client project. This client is an umbrella organization that oversees around 40 smaller organizations located across the country. In the parent org's parlance, these are each projects. One of the umbrella organizations biggest requests was that each child organization should be able to add and edit their own information, but they should not have access to other organizations' content. Using EE's highly flexible user group system, I set up a separate member group with accounts for each child organization. To get things started, I created one post in a "Projects" weblog per child organization and assigned the associated member as the author. Each entry would then be displayed on a separate page at /projects/organization_name. Because I set the permissions for this member group such that each member has access to only their material, on login to the Control Panel, each org would see only their main page in the edit tab.

But then I ran into a problem. Each org also needed to be able to post news items to the /news/ section with headline links printed to their main project page. Related entries were out of the question, because that would require me to present the child organizations with a drop down of all other child organizations, and leaving open the possibility that they could inadvertently associate their news item with the wrong organization.

I realized that the one thing the project and news items would have in common on each project page was their author. I would simply run a query on the News weblog nested within the main exp:weblog:entries call, using the author id from the former in the latter. (Phew!) What I wanted was this:

{exp:weblog:entries weblog="projects" url_title="{segment_2}"}
...project body...
{exp:weblog:entries weblog="news" author_id="{author_id}" limit="5"}
...news items...
{/exp:weblog:entries}
{/exp:weblog:entries}

But as noted, you can't nest calls to exp:weblog:entries.

exp:query to the rescue.

What you can do is nest exp:query inside exp:weblog:entries. You'll run into some variable collision (that is, EE will get confused about which "title" you mean – the project title or the news title – but that can be fixed by either aliasing the variable names or by embedding the exp:query call in a sub-template and passing it an embed variable with the correct author id. I chose the latter. So I ended up with this:

{exp:weblog:entries weblog="projects" url_title="{segment_2}"}
...project body...
{embed="includes/news_headlines" author_id="{author_id}"}
{/exp:weblog:entries}

The embedded template contained this:

{exp:query sql="SELECT title,url_title,entry_date FROM exp_weblog_titles WHERE weblog_id=10 AND author_id={embed:author_id} ORDER BY entry_date DESC LIMIT 5"}
...news headlines...
{/exp:query}

Voila! Embedded content. Granted, the SQL part is daunting if you're new to the Tao of the database, but with a little studying, anyone can cook up a userful query.

Comments

August 20, 2008

10:11 am

It’s great to read your ideas about Expression Engine, I’ve been learning it, too.

Could this have been solved with a stand-alone entry form (SAEF) and a conditional limiting the available categories, depending on member group?

(I have no idea if that can work, haven’t made a custom SAEF myself yet.)

Could you then pull in the display from both weblogs using conditionals?

Diana Brewster (#)

August 21, 2008

11:07 am

Thanks for stopping by, Diana. Hope you’re enjoying EE as much as I have been.

An SAEF’s primary benefit (in my opinion, at least) is that it gives your site contributors a way to post to the site without logging into the control panel. But in this case, it was easier to let them have access to the CP since they need to be able to post to multiple weblogs.

You *may* be able to limit the available categories in an SAEF (I haven’t tried either), but then I would actually need a separate member group for each and every organization, making things unnecessarily complicated.

In re: the display of the news items - an SAEF wouldn’t help that issue. It is possible that I could have created a category-based system of relating entries from one weblog to another, but I generally find it’s best to avoid categories in this kind of situation.

Matt Dawson (#)

August 21, 2008

3:20 pm

thanks for the follow-up, Matt!

If it were me, even though there’s only one account for each member group (child organization), I might have used member group assignment despite the upfront time expenditure, because you can control permissions and capabilities very nicely with the member class; IIRC it’s pretty deep. To me, it’s more intuitive than managing user permissions and views on an author-account basis. It also gives you the freedom to change or add authors, if you want to, without affecting code.

I wonder if creating the 40-odd member groups would then have made related entries workable?

It’s interesting to see see how powerful the SQL query module can be, especially if you have a web application associated with the EE blog.

Diana Brewster (#)

August 21, 2008

4:31 pm

So let me check, though: are you saying you’d create a separate member group for each organization? The reason that’d be overkill in this case is that the access rules for every organization are the same. That is, the user needs to have access to the same set of weblogs, and they must be granted access to only content they created. If I were to create a separate member group per organization, I’d be repeating the same member group settings 40 times.

I see what you’re saying: I *could* grant or restrict access within the member group, but if I can accomplish the same thing by pivoting on whether an org authored an item (meta data which each entry has *automatically*), I’d prefer to do that.

But that’s this situation. I can definitely see other situations where the build you suggest would be a better solution.

Matt Dawson (#)

August 21, 2008

4:58 pm

the user needs to have access to the same set of weblogs, and they must be granted access to only content they created

You certainly have an elegance in the way you solved it; otherwise, for 40 member groups, you’d have to create 40 member-only-usable blogs as well (assigned-user-only accessible content). On the other hand, it’s easy to make a new member group based on an existing member group and assign a new name to each; or this could be done directly in the database if it’s too time-consuming. I suppose if there was a chance that you’d have more than one author in a group you’d have to do it that way?

(thanks for the opportunity to think about how to do stuff in EE! It’s fun to work with, and there’s flexibility in solutions.)

Diana Brewster (#)

August 21, 2008

5:01 pm

Yeah, that’s exactly one of the contingencies I’d imagined. If you had multiple authors per org, things would get more complicated.

Glad you appreciated this! I’ve still got a half dozen EE post ideas. They’ll all be up here eventually. If it didn’t take so long to code the code examples, I’d probably get to them faster!

Matt Dawson (#)