Join from the same table

Question

Join from the same table

GunslerRage Sep '15

Created Sep '15	Last Reply Sep '15	Replies 1	Views 298	Votes 0

GunslerRage 40.6k

Sep '15

I'm trying to join tables PostsCategories

What i'm trying to do is print all the categories, but if a categorie has a parent categorie, print the categorie :-)

Hope you understand!

( What i am trying so far )

        $this->modelsManager->createBuilder()
        ->columns(array('PostsCategories.name', 'parent_categorie.parent as parent'))
        ->from('PostsCategories')
        ->join('PostsCategories as parent_categorie')
        ->orderBy('PostsCategories.id DESC')
        ->limit('9')
        ->getQuery()
        ->execute();

Lajos Bencz · Answer 1 · 2015-09-09T04:47:37-07:00

You'll need an alias for both tables:

 $this->modelsManager->createBuilder()
        ->columns('c.name, p.name parent')
        ->from(array('c'=>'PostsCategories'))
        ->join('PostsCategories', 'c.parent_id=p.id', 'p')
        ->orderBy('c.id DESC')
        ->limit(9)
        ->getQuery()
        ->execute();

Docs: https://docs.phalcon.io/en/latest/api/Phalcon_Mvc_Model_Query_Builder.html

GunslerRage · Answer 2 · 2015-09-09T09:54:08-07:00

I'm trying to do something like this:

Categories:

Test 1
test 2
test 3
    Test 1
    test 2
    test 4
test 4
test 5
    test 1
    test 2

How do i do this??