Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Kind of complicated queries (example)
  • Hi, I'm kinda stuck here. So I just ilmported a whole database of about 300mb.
    Now this is an example of how my query would look normally:
    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
    

    How do I translate this in ORM? I can't find any information on how to implement aliases and a SELECT in a FROM clausule.
  • Well the thing is, I'm not modifying it.
    The error occurs when setting it to a data for the view.
    $this->template->content = View::factory('test/skills')
       ->set('skills', $query); // <- here
    

  • 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);
    
    
  • @Huzzi: You can't combine the DB class with ORM I think. As WanWizard said, you wouldn't be able to update it as it is an object result.
    Which I get! But the point is I'm not modifying it, just setting the result equal to a variable for the view.
  • 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.
  • I'm confused... My view just shows the result
    <?php foreach ($skills as $skill): ?>
        <tr>
            <td width="5%"><?php echo $skill->groupId ?></td>
            <td width="10%"><?php echo $skill->groupId ?></td>
            <td><?php echo $skill->groupId ?></td>
            <td><?php echo $skill->groupId ?></td>
        </tr>
        <?php endforeach; ?>
    

    By sanitizing, you mean sql injection prevention? Why would that be done if there is no form? So how would I need to combine my query with ORM?
    What do I need to do for it to work?
  • 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.
  • Well I suppose I understand the ORM feature... I mean I don't have any problems with writing them.
    But it's the docs that kind of contradict.
    Orm is short for Object Relational Mapper which does 2 things: it maps your database table rows to objects and it allows you to establish relations between those objects.

    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?
  • 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.
  • You don't. The ORM isn't make for custom queries, it is made to deal with records and relationships between them. If you want something complicated, just use the DB class directly.
  • 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
  • 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).

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion