Question about using SqlBuilder class

houseinprogress · July 2006

I'm starting to get my head around Mark's classes but I'm having a really hard time converting a SELECT statement I'd normally do in php to the SqlBuilder class. Can someone give me a little feedback?

Here's the normal statement... [edit: I can't seem to delete this whitespace...]

SELECT e.cid, e.id, e.url, e.title, SUBSTRING( e.description, 1, 250 ), IFNULL(e.pubdate, e.added), f.siteurl, f.title, 

FROM feeds_item e INNER JOIN feeds_channels f ON (e.cid = f.id) 

ORDER BY LEAST(e.pubdate, e.added) DESC

LIMIT 100"

...and (borrowing heavily from Michael's User Messages extension) here's what I've come up with so far in Vanilla...

(Note: In this example I've already defined my DatabaseTables and DatabaseColumns.)

function GetRecentEntries($UserID) {

		$s = $this->Context->ObjectFactory->NewContextObject($this->Context, "SqlBuilder");
		$s->SetMainTable("feed_channels", "m");
		$s->AddSelect(array("id", "title", "url", "siteurl", "vanillaid"), "m");
		$s->AddJoin("feed_items", "n", array("cid", "added", "title", "url", "description", "pubdate"), "cid=id");
		$s->AddWhere("m", "vanillaid", "", $UserID, "="); // This wasn't in the original SELECT, it's new.
		$s->AddOrderBy("pubdate", "m", "DESC"); // How could I make the order be LEAST(pubdate,added)?
		$s->AddLimit("", "10");
				
		$DataSet = $this->Context->Database->Select($s, $this->UserID, "GetEntries", "An error while getting entries.");

		return $DataSet;
		
	}

Am I on the right track? My use of AddJoin seemed particularly questionable as I couldn't figure out what the $ExistingAlias should be. Also where would I insert my SUBSTRING(x,y) to shorten a particular string...

Mark · July 2006

First of all, let me explain why you'd want to use the sqlbuilder at all:

The idea behind the sqlbuilder is that it is a generic way of building sql that doesn't involve you actually using any sql. The purpose of the sqlbuilder is to allow other sqlbuilders to be created and used in it's place, allowing the data to be requested from a different data source with a different syntax. So, the sqlbuilder is really intended to be used when porting Vanilla to a different database server than mysql, like postgresql for example.

If Vanilla was only ever intended for mysql and never to be used with any other database engine, I would have just left the sqlbuilder out of it and gone with straight sql.

The SqlBuilder builds strings of SQL. The database object takes those strings of sql from the builder and manages connecting to the various databases and performing selects, inserts, updates, deletes.

So, if your extension is only ever going to be used with mysql, you don't necessarily need to use the sql builder. You could just pass a straight string of sql to the MySQL database object and call it's Execute method (which allows you to execute a straight string of sql).

Back to your query:

SELECT e.cid, e.id, e.url, e.title, SUBSTRING( e.description, 1, 250 ), IFNULL(e.pubdate, e.added), f.siteurl, f.title
FROM feeds_item e 
INNER JOIN feeds_channels f ON (e.cid = f.id)
ORDER BY LEAST(e.pubdate, e.added) DESC
LIMIT 100

The first big problem I see with this is SUBSTRING( e.description, 1, 250 ), IFNULL(e.pubdate, e.added). The function calls in the select list is not supported in the SqlBuilder for three reasons:

1. It's a pain in the butt to code, making the object a little slower and more convoluted.
2. by limiting what you can do in a select list, it makes the string more likely to work in all versions of mysql.
3. those two functions aren't really necessary, and you can do them on the php end of things after you've retrieved the data.

I've considered adding select functions in the past, and haven't done it yet. I may still do it in the future...

So, to do your query without the functions in the select list, I'd do...

$s = $this->Context->ObjectFactory->NewContextObject($this->Context, 'SqlBuilder');
$s->SetMainTable('feeds_item', 'e');
$s->AddSelect(array('cid', 'id', 'url', 'title', 'description', 'pubdate', 'added'), "e");
$s->AddSelect(array('siteurl', 'title'), 'f');
$s->AddJoin("feeds_channels", "f", 'id', 'e', 'cid', 'inner join');
$s->AddOrderBy(array('pubdate', 'added'), array('e', 'e'), 'desc', 'least');
$s->AddLimit(0, 100);
return $this->Context->Database->Select($s, $this->UserID, "GetEntries", "An error while getting entries.");

If that is beyond you or just too confusing and you know you only ever want your extension used on mysql, just use your sql string and do...

return $this->Context->Database->Execute($sql, $this->UserID, "GetEntries", "An error while getting entries.");

houseinprogress · July 2006

Thanks Mark - very helpful response. Given what I'm trying to accomplish I think that Execute is the best solution but your walkthrough and the final SqlBuilder actually still helped me get my head around that class... As always, you rock.

Question about using SqlBuilder class

Comments