Newbie Q - controller/view code for mixed query

[eluser]Nick_MyShuitings[/eluser]
You're correct in your analysis, but you're one of the few who've actually made that analysis. Most think AR is like some CodeIgniter dark magic for interacting with the database... and then they make all sorts of hilarious posts like "OMG I need to make this SQL with AR" with things like complex nested queries etc.

So I choose to play the role of "anti AR" just to get people thinking... its a great helper, easy crutch, but all of its security can be had with query bindings for selects... and you ditch an abstraction layer...

Personally its the same reason I avoid ORMS... just a personal choice. Congrats on actually thinking it through though.

[eluser]osci[/eluser]
Well the problem is that most of these people just grab a framework, have minimum knowledge of basic stuff, regarding php, sql etc, and start to think as you say about "magic" ways.

There is no solution to something like that. They have to study and the are lazy / bored (how can you be bored, every time I code something I feel like creating, and I'm not a session programmer, I do this for a living many years now and I still feel the same, although my wife doesn't )

You would have to play the "anti framework" to get people thinking, but that's too much!!

AS of ORMs I don't use either till now, but I feel I should (for portability in case I have to use another framework maybe - reading through doctrine 2 docs nowadays).

[eluser]vinofilus[/eluser]
This is a helpful discussion! I was wondering about trade offs between sql and AR. This is good food for thought. Thanks.
And as I've spent more time getting models figured out, the whole thing is making more sense.
As a training exercise, I'm going to start this project over again and do it differently. Hopefully smarter.
Thanks all.
Les...

[eluser]jmadsen[/eluser]
would love to weigh in on the "stop using AR" discussion, on the side of learning to write sql first and using AR for its object/re-usability attributes, but for now I'll show the query:


@vinofilus - if you want task info, list of comments plus comment count, you need:

Code:

SELECT tasks.*, comments.*, COALESCE(mydata.comment_count, 0)
FROM tasks
LEFT OUTER JOIN comments ON (tasks.id = comments.task_id )
LEFT OUTER JOIN  (SELECT c2.task_id, count(*) AS comment_count
    FROM comments c2
    GROUP BY c2.task_id) as mydata ON mydata.task_id = tasks.id
ORDER BY tasks.id asc, comments.id asc

put this is a loop in your view, comparing task id in each iteration to see if it changes (and yo uneed to show task info and comment count again)

[eluser]Nick_MyShuitings[/eluser]
Your very showing of that very nice looking query (two thumbs up) is its own argument in the discussion... that query would be impossible to do in AR unless its been severely upgrades since the last time I bothered to study it.

[eluser]jmadsen[/eluser]
yes, will be one of my arguments when I get around to it ;-)

not impossible, but basically you just show a bunch of sql into a function, so not really using AR. There aren't any "native" AR functions to make subqueries

[eluser]osci[/eluser]
Native yes
Undocumented yes
unsupported yes

Check this

Code:

$this->db->from('comments');
$this->db->select('task_id, count(*) AS comment_count');
$this->db->group_by('task_id');
// ------------
// "magic" here
$subquery = $this->db->_compile_select();
$this->db->_reset_select();
// ------------
$this->db->from('tasks');
$this->db->select('tasks.*, comments.*, COALESCE(mydata.comment_count, 0)');
$this->db->join('comments','tasks.id = comments.task_id','LEFT OUTER');
$this->db->join("($subquery) as mydata",'mydata.task_id = tasks.id','LEFT OUTER');
$this->db->order_by('tasks.id','ASC')->order_by('comments.id','ASC');
$this->db->get();

You compile the select, thus produce the statement, store it and use it later

I haven't set up a db so it produces error, but the error gives me the above sql statement.

Drawback is _magic is unsupported.

EDIT:
Actually the above code produces error. I hadn't noticed the returned sql string.
To get correct sql statement you need to do

Code:

$this->db->select('tasks.*, comments.*, COALESCE(mydata.comment_count, 0)',FALSE);

[eluser]jmadsen[/eluser]
sure, but again - all you're doing is using a bunch of functions that build strings. I can't see any point whatsoever to using your code, except as a "look what I can do" excercise.

If you don't know enough sql to write the query in a straightforward manner in the first place, you'll never figure out to use your _magic functions to force the desired result.

Conclusion: learn some basic "SQL 101"

[eluser]osci[/eluser]
I just showed that it can be accomplished with AR, not a "look what I can do". Nothing more.
You need to know how to write sql to produce the statement both in sql and in AR.
Also I never said that you should produce the previous sql with AR.

But look at it a little further. Imagine needing this subquery for 2 sql statements. Create it once and feed it to the main queries. Not that this is useful but still... Or solve a situation that you need to make a lib to handle things like that in a more generic way. Which you might also produce with straight queries, but still...

Let's say we have news and blog table, both having comments and have the same view requirements as above. Why not have a common function to return the created subquery just by passing the needed tables in this case? You could make your code more abstract to handle both scenarios and after everything is ok almost forget about it.

To sum up, all of the above were examples of what could be achieved, useful or not. It's not only a matter of knowing sql, is to what extent you feel you need to get abstract. SQL is fine and so is AR. Everybody chooses what he likes.

[eluser]theprodigy[/eluser]
Just to add my $0.02.

Do I know how to write SQL? Yes.
Do I prefer to use AR? Yes.

Mainly for simple reasons. It allows much more flexibility since the SQL statement isn't compiled until you call the get() function.

One example:

Check this

Code:

$this->db->from('comments');
$this->db->select('task_id, count(*) AS comment_count');
$this->db->group_by('task_id');
// ------------
// "magic" here
$subquery = $this->db->_compile_select();
$this->db->_reset_select();
// ------------
$this->db->from('tasks');
$this->db->select('tasks.*, comments.*, COALESCE(mydata.comment_count, 0)');
$this->db->join('comments','tasks.id = comments.task_id','LEFT OUTER');
$this->db->join("($subquery) as mydata",'mydata.task_id = tasks.id','LEFT OUTER');
$this->db->order_by('tasks.id','ASC')->order_by('comments.id','ASC');
$this->db->get();

Notice osci put the from() before the select() in the "after magic" part? You can do this with AR.

Also,

Code:

foreach($tasks as $task)
{
    $this->db->or_where('task_id',$task->id);
}

is a little easier to write (and read), than something like:

Code:

$where = array();

foreach($tasks as $task)
{
    $where[] = 'task_id = ' . $task->id;
}

$where_clause = implode(' OR ', $where);

Does AR have it's limitations? Yes, but as osci showed, you can still do what you need to do (even if it is not the expected way to do it).

Don't get me wrong. I agree with the reasoning that you should know what you're doing before you start taking shortcuts, but I also believe that if you do know what you're doing, it's not bad to take shortcuts (as long as they still get the job done right).

For those that are battling against AR because it doesn't make sense (or just adds a wrapper), let me ask you this. Do you handcode all your javascript or do you use jQuery (or another js framework)? If you use a js framework, why? Why not just handcode all your javascript? It's just basically a wrapper anyway.