Joins: Controller or Model?

[eluser]Denarius[/eluser]
I've literally been playing with Code Igniter for a couple of days and I'm on the brink of kicking off a project that uses a (moderately) complicated business model. I'm happy that I've created a suitable database model and have moved on to considering views and application logic, which is the hard part as far as I'm concerned!

For one of my views it will be necessary to query several tables and deliver the information about a particular event. In one particular case this is going to require an inner join across three tables in order to establish what performer is playing what instrument for a performance. Ultimately my question boils down to whether the join query belongs in the model or in the controller? My instincts are telling me controller since it's a manipulation of the data for presentation, but at this stage the model seems to be virtually redundant as Code Igniter seems to be inferring everything from the database structure. Also, the tutorials I've looked at seemed to ignore the model and go straight for $this->db->query in the controller itself, which leaves me wondering where the model classes belong in all this?

I'm grateful for any insight that anybody can offer on this.

[eluser]n0xie[/eluser]
The usual way is to make a model per database table. If you have some query where you need inner joins this would go into the model normally.

[eluser]Denarius[/eluser]
Hi, thanks for your reply. Having joins carried out by methods in the model seems reasonable, but what's the rationale for having one model per table? Ultimately, aren't you just replacing $this->db->get('tablename') with $this->db->myMethodThatOnlyContainsTheOtherCommandAnyway?

[eluser]n0xie[/eluser]
http://en.wikipedia.org/wiki/Object-relational_mapping

[eluser]Denarius[/eluser]
Thanks for pointing me to the article. Lets see if I've got the gist of it.

As far as I can see, the Model class in the framework is doing the object-relational mapping as queries return rows of objects based on the fields in the relevant table. In the case of a join though you would need to extend the model with the join query. In my specific case I'm looking at compositions and arrangements of compositions, so in that specific case I would be extending the arrangements model to include the left join query beyond the standard queries as determined implicitly by the Model class in the main library.

Am I on the right track or am I still missing something?

Update: I meant compositions and arrangements in a musical sense rather than compositions in objective framework programming. Should've explained that. :red:

[eluser]dark_lord[/eluser]
Actually you can use traditional SQL Statement in CodeIgniter to create join statements much easier (as for me) to replace the built in active record methods found in CI, you can refer on this part of the CI User Guide (found in the below link).

Code:

$query = $this->db->query("SELECT b.fieldA as fieldA, b.fieldB FROM table1 a, table2 b WHERE a.id = b.id");

foreach ($query->result() as $row)
{
    echo $row['fieldA'];
}

http://ellislab.com/codeigniter/user-gui...eries.html

Let me know if it helps!

[eluser]Denarius[/eluser]
Thank you very much for the pointer with conventional SQL queries. Is there any performance gain in writing traditional queries against using the ActiveRecord constructs? My database tables are now built and I'll be writing my very first non-tutorial CodeIgniter MVC set tomorrow. In the meantime, bed!

[eluser]n0xie[/eluser]
[quote author="Denarius" date="1267680487"]Is there any performance gain in writing traditional queries against using the ActiveRecord constructs? [/quote]
Yes, writing your own SQL will almost always be faster, since the AR has to 'guess' what you intend to do when it builds your query. AR is a handy tool for writing 'simple' queries. I would write more complex queries by hand: it's faster and you know exactly what's going on.

[eluser]danmontgomery[/eluser]
[quote author="n0xie" date="1267743710"][quote author="Denarius" date="1267680487"]Is there any performance gain in writing traditional queries against using the ActiveRecord constructs? [/quote]
Yes, writing your own SQL will almost always be faster, since the AR has to 'guess' what you intend to do when it builds your query. AR is a handy tool for writing 'simple' queries. I would write more complex queries by hand: it's faster and you know exactly what's going on.[/quote]

This is technically true, AR has to construct your query, but once it's constructed the actual query is run exactly the same way... For the overwhelming majority of applications this difference will be negligible. I do agree with n0xie, though, that for complex queries it's faster (for me) to write them by hand, mainly to avoid AR incorrectly escaping queries, or because of functionality that's missing (subqueries, grouping where clauses, etc).

[eluser]Denarius[/eluser]
[quote author="wish_bear" date="1267664633"]Actually you can use traditional SQL Statement in CodeIgniter to create join statements much easier (as for me) to replace the built in active record methods found in CI, you can refer on this part of the CI User Guide (found in the below link).

Code:

$query = $this->db->query("SELECT b.fieldA as fieldA, b.fieldB FROM table1 a, table2 b WHERE a.id = b.id");

foreach ($query->result() as $row)
{
    echo $row['fieldA'];
}

http://ellislab.com/codeigniter/user-gui...eries.html

Let me know if it helps![/quote]

I've just reread this post and suddenly realised that with a custom query, each row is returned as an array in contrast to using a
$this->$db->get('tablename') which returns rows as objects corresponding to the table (i.e. $row->fieldname. I recognise that it's just as easy to echo the values in the view either way, but with a manual query would you normally create a class yourself to keep the model in line with how the framework does things?

Many thanks for the discussion, it's very thought-provoking.