We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

How to retrieve related data with this relationship?

With the following posts and metas data table

metas
+------+-----------+----------+
|  id  |   name    |   type   |
+------+-----------+----------+
|   1  |    news   | category |
|   2  |   sports  |   tag    |
|   3  |    music  | category |
+------+-----------+----------+

posts
+------+-----------+
|  id  |   title   |
+------+-----------+
|   1  |   aaaaa   |
|   2  |   bbbbb   |
|   3  |    ccc    |
+------+-----------+

posts_metas
+------------+--------------+
|  posts_id  |   metas_id   |
+------------+--------------+
|     1      |    1         |
|     1      |    3         |
|     1      |    2         |
+------------+--------------+

How to retrieve the metas with type="category" of the post with id=1? the result will almost be like this:

+------------+-----------+-----------+----------+
|  posts_id  |  metas_id |   name    |   type   |
+------------+-----------+-----------+----------+
|    1       |     1     |   news    | category |
|    1       |     3     |   music   | category |
+------------+-----------+-----------+----------+


27.0k

I think left join (sql) will easy solve this



31.3k
edited Nov '15

$post = Posts::findFirstById(1);

...

and then, how to code?



145.0k

dont use findBy, just use modelsManager and joins.



31.3k
in reply to Wojciech Ślawski 
$phql = "SELECT ....";
$this->modelsManager->executeQuery($phql);

something like this? and where to write this code? In the Posts model, or Metas model, or PostsMetas model?

dont use findBy, just use modelsManager and joins.



145.0k
edited Nov '15

No, not like this, like this:

$this->modelsManager->createBuilder()
    ->columns(['posts_id','metas_id','name','type'])
    ->from(['PostsMetas'=>'Namespace'])
    ->leftJoin('Metas','condition','namespace')
    ->leftJoin('Posts','condition','namespace')
    ->where('type = :type: AND Metas.id = :id:',['type'=>'category','id'=>1])
    ->getQuery()
    ->execute()


31.3k
in reply to Wojciech Ślawski

Thanks!

where should I to put the code? In a controller? or in a model?

No, not like this, like this:

$this->modelsManager->createBuilder()
  ->columns(['posts_id','metas_id','name','type'])
  ->from(['PostsMetas'=>'Namespace'])
  ->leftJoin('Metas','condition','namespace')
  ->leftJoin('Posts','condition','namespace')
  ->where('type = :type: AND Metas.id = :id:',['type'=>'category','id'=>1])
  ->getQuery()
  ->execute()