FuelPHP Forums

Not sure if this line is correct

INNER JOIN invTypes ON ( invTypes.groupID = groups.groupID AND invTypes.published = 1 )

try

INNER JOIN invTypes ON ( invTypes.groupID = groups.groupID)->where('nvTypes.published',1);

Harro Verton wrote on Wednesday 18th of May 2011:

The DB::query()->execute() is the way to go. You get the error message because somewhere in your code you try to manipulate the query result. It is returned as a result object, which you can't modify (i.e. you can not assign anything to it).

You don't, but the View class does. It will sanitize the variables passed for output. Nothing will block you from using a mix of ORM and DB calls, I use that myself in my nestedsets model extension. Some queries (like for example update's) can't be run through ORM because it's focused on the loaded objects.

About sanitizing the output (or more exactly encoding): http://fuelphp.com/docs/general/views.html#security About the Orm: I would suggest you first try to fully grasp what an Orm does before trying to rewrite custom queries to it. Basicly it comes down to the following: the Orm does not allow custom queries such as those. An Orm like our ActiveRecord implementation (which has nothing to do with what CodeIgniter thinks AR is) maps each row in the DB per table to an object. If you want stuff like your query you need to keep doing custom queries. When you move to an Orm, first try to understand the idea behind it and then decide whether it's right for you. Don't try to do the same things with an Orm as you'd do with a query builder, the approach is way to different for a 1-to-1 translation.

The docs don't contradict in your example, only if you interpret them wrong - otherwise the content you quoted is right on the money.

So the ORM as the O stands for returns an object. WanWizard tells me the error I get is caused of the result I get, was an object. So what's the difference?

"it maps your database table rows to objects", thus each individual table row from each individual table is a separate object. That is completely different from the DataBase_Result object that the Query Builder returns. That just wraps the resultset and allows you to have some utility methods on the result. I'm sorry but from the way you interpret all of this it's clear you don't fully understand what the Query Builder does and what an Orm does. I'd suggest reading up on Orms on Wikipedia.
The Query Builder does exactly what it says: it helps you build a query. With a QB you don't write your query yourself but build it using object oriented means by calling methods on a Query object. Which is what happens in your examples.

Well I tried using the class

$query = DB::select('SELECT groups.groupID AS groupID, 
   groups.groupName AS groupName,
   invTypes.typeName AS skillName,
   invTypes.description AS skillDescription,
   invTypes.typeID
   FROM ( SELECT * FROM invGroups WHERE categoryID = 16 AND published = 1 ) AS groups
   INNER JOIN invTypes ON ( invTypes.groupID = groups.groupID AND invTypes.published = 1 )
   ORDER BY groupName')->execute();

but I get some sql error:

1064! Fuel\Core\Database_Exception [ 1064 ]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`typeName AS skillName, invTypes`.`description AS skillDescription, invTy' at line 1 [ SELECT `SELECT groups`.`groupID AS groupID, groups`.`groupName AS groupName, invTypes`.`typeName AS skillName, invTypes`.`description AS skillDescription, invTypes`.`typeID FROM ( SELECT * FROM invGroups WHERE categoryID = 16 AND published = 1 ) AS groups INNER JOIN invTypes ON ( invTypes`.`groupID = groups`.`groupID AND invTypes`.`published = 1 ) ORDER BY groupName` ]

And I can't seem to figure out what the error is.
Fyi:
If I use DB::query instead of select, I get following error:

Fuel\Core\Fuel_Exception [ Error ]: Database results are read-only