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 |
+------------+-----------+-----------+----------+


13.6k

I think left join (sql) will easy solve this



10.1k
edited Nov '15

$post = Posts::findFirstById(1);

...

and then, how to code?

dont use findBy, just use modelsManager and joins.



10.1k
$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.

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()


10.1k

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()