• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
New to Joins and codeigniter

#1
[eluser]liam1412[/eluser]
I am new to joins; I have always coded the clumsy way with loads of queries, and using MVC this isn't so easy without having loads of code in your view.

I have 3 tables

categories
forums
posts

What I need is a multi dimensional array that would hold a list of categories and then within each category a list of forums that reside in that category and then within each forum, a post count/thread count.

With my old procedural way I can easy acheive what I want without using arrays, but with MVC I am struggling.

can anyone help me out.

Thanks from a newb to OOP/Mvc/COdeigniter all at once :-)

#2
[eluser]alboyd[/eluser]
This isn't a question about MVC - simply create your query no?

Code:
SELECT cats.category_name, f.forum_name, COUNT(p.post_id) FROM categories cats
JOIN forums f ON cats.category_id = f.category_id
JOIN posts p ON f.forum_id = p.forum_id
WHERE p.deleted IS NULL
GROUP BY cats.category_name, f.forum_name

Obviously I have just completely made up what the field names would be but this would be the kinda idea of what you want isn't it?

#3
[eluser]jedd[/eluser]
First, post your schema rather than just your table names.

[quote author="liam1412" date="1251864552"]
What I need is a multi dimensional array that would hold a list of categories and then within each category a list of forums that reside in that category and then within each forum, a post count/thread count.

With my old procedural way I can easy acheive what I want without using arrays, but with MVC I am struggling.
[/quote]

I'd be fascinated to see how you juggled such hierarchical information absent a single array - can you post your extant code for pulling these data in?

#4
[eluser]liam1412[/eluser]
Here are the tables as requested.

forums

forum_id
forum_name
category_id
display_rank

forum_categories

category_id
category_name
category_text
display_rank

forum_posts

post_id
post_title
post_text
forum_id
response_to
time_posted

#5
[eluser]alboyd[/eluser]
[quote author="liam1412" date="1251885338"]Here are the tables as requested.

forums

forum_id
forum_name
category_id
display_rank

forum_categories

category_id
category_name
category_text
display_rank

forum_posts

post_id
post_title
post_text
forum_id
response_to
time_posted[/quote]

wow looks like my query would work straight out of the box for that schema Smile So was that helpful or totally not what you were asking?

#6
[eluser]liam1412[/eluser]
No it was exactly what I wanted, I modified it slightly to fit my schema but I get an empty result set, apart from all the other bumf codeign gives.

Code:
$data['result'] = $this->db->query('
    SELECT cats.category_name, f.forum_name, COUNT(p.post_id) FROM forum_categories cats
    JOIN forums f ON cats.category_id = f.category_id
    JOIN forum_posts p ON f.forum_id = p.forum_id
    GROUP BY cats.category_name, f.forum_name
    ');
    $this->load->view('homepageview',$data);

Gives

Code:
object(CI_DB_mysql_result)#12 (7) {
  ["conn_id"]=>
  resource(28) of type (mysql link persistent)
  ["result_id"]=>
  resource(29) of type (mysql result)
  ["result_array"]=>
  array(0) {
  }
  ["result_object"]=>
  array(0) {
  }
  ["current_row"]=>
  int(0)
  ["num_rows"]=>
  int(0)
  ["row_data"]=>
  NULL
}

#7
[eluser]alboyd[/eluser]
hey mate - break out the query in a query window in like Yog or whatever you use and figure it out from there.

You might need to change the join directions etc?

#8
[eluser]liam1412[/eluser]
I took out the posts bit but that doesn't seem like I would be able to use that in an easy way. It gives

Code:
object(CI_DB_mysql_result)#12 (7) {
  ["conn_id"]=>
  resource(28) of type (mysql link persistent)
  ["result_id"]=>
  resource(29) of type (mysql result)
  ["result_array"]=>
  array(0) {
  }
  ["result_object"]=>
  array(5) {
    [0]=>
    object(stdClass)#13 (2) {
      ["category_name"]=>
      string(16) "Off Topic Forums"
      ["forum_name"]=>
      string(11) "Off Topic 1"
    }
    [1]=>
    object(stdClass)#14 (2) {
      ["category_name"]=>
      string(11) "Site Forums"
      ["forum_name"]=>
      string(9) "Site News"
    }
    [2]=>
    object(stdClass)#15 (2) {
      ["category_name"]=>
      string(11) "Site Forums"
      ["forum_name"]=>
      string(10) "Site Rules"
    }
    [3]=>
    object(stdClass)#16 (2) {
      ["category_name"]=>
      string(14) "Subject Forums"
      ["forum_name"]=>
      string(15) "Subject Forum 1"
    }
    [4]=>
    object(stdClass)#17 (2) {
      ["category_name"]=>
      string(14) "Subject Forums"
      ["forum_name"]=>
      string(15) "Subject Forum 2"
    }
  }
  ["current_row"]=>
  int(0)
  ["num_rows"]=>
  int(5)
  ["row_data"]=>
  NULL
}

I had another crud class where you could declare a relationship and it would bring this sort of result.

Code:
Array
(
    [0] => Array
        (
            [cat_id] => 1
            [cat_name] => Category 1
            [cat_desc] => This is category 1
            [display_rank] => 0
            [private] => 0
            [forum_forums] => Array
                (
                    [0] => Array
                        (
                            [forum_id] => 2
                            [forum_cat_id] => 1
                            [forum_name] => Forum 1
                            [forum_desc] => This is forum 1
                            [display_rank] => 0
                            [lastpost_by] => 0
                            [lastpost_time] => 0
                        )

                    [1] => Array
                        (
                            [forum_id] => 5
                            [forum_cat_id] => 1
                            [forum_name] => This is another forum
                            [forum_desc] => This forum is about stuff
                            [display_rank] => 0
                            [lastpost_by] => 0
                            [lastpost_time] => 0
                        )

                )

        )

    [1] => Array
        (
            [cat_id] => 2
            [cat_name] => Category 2
            [cat_desc] => This is category 2
            [display_rank] => 0
            [private] => 0
            [forum_forums] => Array
                (
                    [0] => Array
                        (
                            [forum_id] => 3
                            [forum_cat_id] => 2
                            [forum_name] => Forum 2
                            [forum_desc] => This is forum 2
                            [display_rank] => 0
                            [lastpost_by] => 0
                            [lastpost_time] => 0
                        )

                )

        )

    [2] => Array
        (
            [cat_id] => 3
            [cat_name] => Category 3
            [cat_desc] => This is category 3
            [display_rank] => 0
            [private] => 0
            [forum_forums] => Array
                (
                    [0] => Array
                        (
                            [forum_id] => 4
                            [forum_cat_id] => 3
                            [forum_name] => Forum 3
                            [forum_desc] => This is forum 3
                            [display_rank] => 0
                            [lastpost_by] => 0
                            [lastpost_time] => 0
                        )

                )

        )

)


With this I could use a foreach loop to get the categories, then within that foreach I could use a foreach to get the forums with that category.

Thanks for looking

#9
[eluser]garycocs[/eluser]
Hi Lads,
Sorry to thread hop here, I'm kinda new to the join queries as well and I was just wondering how you would write an Active Record query that lets say:

Got the information from one table and the averages of a number of rows from another table?


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.