ExpressionEngine’s SQL Query Module

Aug. 19, 2008

6: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:

  1. {exp:weblog:entries weblog="projects" url_title="{segment_2}"}
  2. ...project body...
  3. {exp:weblog:entries weblog="news" author_id="{author_id}" limit="5"}
  4. ...news items...
  5. {/exp:weblog:entries}
  6. {/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:

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

The embedded template contained this:

  1. {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"}
  2. ...news headlines...
  3. {/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

6: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

7: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

11:20 am

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

12: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

12: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

1: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 (#)

November 25, 2008

5:33 am

Hi,
I’m new to queries; could I use them to get a comment on the index-page and pagination-pages?
Currently I’m making my own site with EE at the back, there occure some problems;
- comments are only available on the post-page, but I need pagination.
- paginations results in p0, p1, p2 and so on.

Conclusion; I need something ( I guess queries) to import the comments, and EE-tags won’t work, since the url is index, p1, p2 ...

Even better would be pagination between posts and my last most as landingpage. Would that be possible ?
( Forgive me my ignorance but EE is overwhelming.)

Lieven (#)

November 25, 2008

7:22 am

Lieven -

I don’t have a crystal clear idea of what you’re trying to do, but you likely won’t need the query module.

Check out the documentation for the exp:comment:entries tag. Most importantly, check out the dynamic parameter.

What I think you’re seeing is that exp:comment entries expects to get information about which comments to show based on what’s in the url. You can override this behavior by setting dynamic="off". Once you’ve done that, there are other ways to tell the template which entry’s comments you’re interested in. One possibility, depending on your setup, might be dynamically using the entry_id parameter .

Hope that helps!

Matt Dawson (#)

November 25, 2008

7:33 am

Thanks for the answer;

I’ve already used the dynamic parameter for my archive; It doesn’t work for the comments. ( You can see what I mean on www.websigner.be )

I’ll check the entry_id, I hope it solves it.

In brief what I want;
Page with last post, comment & archive, Next / Previous links to posts (same template, so also with comments of post & archive).
I would like index/atrikel in stead of index/p1 when pext/prev is used.
( so users can use the URL for linking )

Lieven (#)

November 25, 2008

7:44 am

You’ll want to stay away from the whole “pagination” concept altogether. What it seems you’re wanting to do is have the title of the entry as the first url segment - which can be accomplished a few different ways. The easiest would actually be to put all the logic for displaying single entries in your site’s main index template.

Chaning the pagination system would be way more trouble than it’s worth. What you’re trying to do can’t be done without hacking at core files, AFAIK.

Matt Dawson (#)

November 25, 2008

7:59 am

I think I did that ( I only use index.html as page), but I don’t see how I could provide next / prev without pagination. But that’s an other issue.
I agree that it would be better without pagination and just sending my last post + comments + archive to my index and create buttons to switch post ( and comments). Only the comments don’t work on my index ( so it’s not affected by eliminating the pagination )

In fact all I want is 1 page, that switches post, comments and url, when you press next or prev.

Just tried the entry_id, doesn’t work, but I’ll be applying it all wrong I guess.

Could I mail you for this ( instead of ruining your comments ) ?

Lieven (#)

December 27, 2008

9:20 am

fseatrhshwoslgfiwell, hi admin adn people nice forum indeed. how’s life? hope it’s introduce branch ;)

Goaklyappelpendergic (#)

February 17, 2009

6:34 pm

You saved my bacon friend. I was banging my head on a wall with the {title} not working as expected. Good call on embeded templates

Dan Diemer (#)

May 15, 2009

12:15 am

I always like to read something like this. That is usually a bit hard to find valuable information on the internet. And I found your post using Yahoo and I can say I the time spent was worth reading.

ReversePhoneLookup (#)

May 25, 2009

10:59 am

Hi,Matt! I’m not sure wether I may ask it from you or not, still… I am working on a mobile version of my site. Have you any idea, what I can do to make this work for mobile too? Thanks for the hint.

Noah - Name (#)

June 1, 2009

11:57 pm

My friend emailed me your post URL. I thought it will be something not worth my time, but I was wrong. Will tell my friend thanks.

Kim (#)

June 17, 2009

2:48 pm

This EE tip was very helpful, Thank you!

Billy Shall (#)

August 14, 2009

3:41 am

My friend emailed me your post URL Great blog with loads of tips. Thanks.

psoriasis cure (#)

September 28, 2009

1:33 pm

I came across this while search for something related on Bing.  Nice read!

ReverseLookup (#)

November 22, 2009

3:39 am

Thank you a lot for the

text about this good topic! People know that the writing service

would offer the essay

writing. Thus, it’s the best chance to buy pre written essay or

href="http://www.exclusivepapers.com">custom essays

about this post.

ridons (#)

Whaddya think?