Nesting queries within active record

[eluser]Colin Williams[/eluser]

I will have to loop through the queries, and propagate an array manually. Is that correct?

Yeah, and it doesn't have to be messy. That's the way it is done if you are going to optimize your queries by employing JOINs. The other option is to run a query for each other item.

[eluser]E.T.Cook[/eluser]
[quote author="Colin Williams" date="1223285266"]

I will have to loop through the queries, and propagate an array manually. Is that correct?

Yeah, and it doesn't have to be messy. That's the way it is done if you are going to optimize your queries by employing JOINs. The other option is to run a query for each other item.[/quote]

The problem I face is this: even if I optimize the queries with a join, I will have to nest some loops to display it correctly...since I would want it to be displayed like this...

Ford
F-150
Focus
Taurus

Audi
A-4
A-6

and not

Ford F-150
Ford Focus
Ford Taurus

So it is 6 in one hand, half a dozen in another.

[eluser]Colin Williams[/eluser]

even if I optimize the queries with a join, I will have to nest some loops to display it correctly

Right. Exactly. I was just saying there's nothing messy about that.

[eluser]E.T.Cook[/eluser]
[quote author="Colin Williams" date="1223286314"]

even if I optimize the queries with a join, I will have to nest some loops to display it correctly

Right. Exactly. I was just saying there's nothing messy about that.[/quote]

It kind of breaks hard MVC methodology. Maybe I am too much of a purist.

[eluser]Colin Williams[/eluser]
Not a purist. You just don't appear to understand what MVC is. When you structure data, guess what you are doing. Modeling it. Please explain how iterating over a query result to structure data "breaks hard MVC methodology."

Edit:

Actually, I think you are confused by our suggestion. We suggest this iteration happens in the model, before returning to the controller and/or view. Not sure why you immediately assumed this has to happen outside the model. Nearly every model I write has a _build_object($result) method that does this very thing.

[eluser]E.T.Cook[/eluser]
[quote author="Colin Williams" date="1223288986"]Not a purist. You just don't appear to understand what MVC is. When you structure data, guess what you are doing. Modeling it. Please explain how iterating over a query result to structure data "breaks hard MVC methodology."

Edit:

Actually, I think you are confused by our suggestion. We suggest this iteration happens in the model, before returning to the controller and/or view. Not sure why you immediately assumed this has to happen outside the model. Nearly every model I write has a _build_object($result) method that does this very thing.[/quote]

You are fairly passive aggressive. Utilizing business logic to delineate a poorly formed data tree breaks MVC, not in practice, but in form. What I have already done, and exactly what I was stating prior, is form the tree correctly from the get go, before it is passed to the view.

[eluser]Sumon[/eluser]
have a look the following code. may be helpful to understand
Controller :

Code:

$Data['Recordsets'] = $this->model_name->MemberProfile($MemberId);
$this->load->view("member/view_all_member_info",$Data);

Model : (model_name.php)

Code:

function MemberProfile($MemberId)
    {
    $query = $this->db->query("SELECT Com.*, Login.mem_id, Login.mem_primary_photo, Profile.mem_first_name, Profile.mem_last_name FROM image_gallery_image_comment Com, member_login Login, member_profile Profile WHERE Com.image_id='$PhotoId' AND Com.mem_id=Login.mem_id AND Profile.member_id=Login.mem_id");
    if ($query->num_rows() > 0)
    {
            $i=0;
            foreach($query->result() as $val):
                foreach($val as $Key=>$Value):
                                  if($Key=="mem_primary_photo") //for example this is your car model(Ford)
                                     $MemberPhoto[$i] = $Value;
                                  else
                                     $AllInfo[$i][$Key]=$Value;
                endforeach;
                $i++;
            endforeach;
            return $Comments;
        }
        else
            return FALSE;
    }

View : (view_all_member_info.php)

Code:

if(count($Recordsets->result())==0)    echo "Sorry no member exists.";
    <?php foreach($Recordsets->result() as $row): ?>
        <?=$row->site_feature_name;?>
    <?php endforeach; ?>

[eluser]Colin Williams[/eluser]
I'm saying you do it in the model before it gets to the view. So we actually agree. Unless perhaps you write and use a stored procedure, your SQL query isn't going to return it in the format you want. You have to manipulate it.

Utilizing business logic to delineate a poorly formed data tree breaks MVC

Again, it has to happen if your SQL query doesn't give you the perfect format. This is what models are meant for. Modeling/forming data.

[eluser]E.T.Cook[/eluser]
[quote author="Colin Williams" date="1223294527"]I'm saying you do it in the model before it gets to the view. So we actually agree. Unless perhaps you write and use a stored procedure, your SQL query isn't going to return it in the format you want. You have to manipulate it.

Utilizing business logic to delineate a poorly formed data tree breaks MVC

Again, it has to happen if your SQL query doesn't give you the perfect format. This is what models are meant for. Modeling/forming data.[/quote]

Looks like all of this is much ado about nothing then...because that was my stance all along.

[eluser]Colin Williams[/eluser]
Haha.. funny how that happens.