Databases
StackOverflow2013
Postsdbo
POHow to nest joins with CakePHP?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to nest joins with CakePHP?
    primarykey
    data
    text
    <p>I'm trying to behave. So, instead of using following SQL syntax:</p> <pre><code>select * from tableA INNER JOIN tableB on tableA.id = tableB.tableA_id LEFT OUTER JOIN ( tableC INNER JOIN tableD on tableC.tableD_id = tableD.id) on tableC.tableA_id = tableA.id </code></pre> <p>I'd like to use the CakePHP <code>model-&gt;find()</code>. This will let me use the <code>Paginator</code> too, since that will not work with custom SQL queries as far as I understand (unless you hardcode one single pagination query to the model which seems a little inflexible to me).</p> <p>What I've tried so far:</p> <pre><code>/* inside tableA_controller.php, inside an action, e.g. "view" */ $this-&gt;paginate['recursive'] = -1; # suppress model associations for now $this-&gt;paginate['joins'] = array( array( 'table' =&gt; 'tableB', 'alias' =&gt; 'TableB', 'type' =&gt; 'inner', 'conditions' =&gt; 'TableB.tableA_id = TableA.id', ), array( 'table' =&gt; 'tableC', 'alias' =&gt; 'TableC', 'type' =&gt; 'left', 'conditions' =&gt; 'TableC.tableA_id = TableA.id', 'joins' = array( # this would be the obvious way to do it, but doesn't work array( 'table' =&gt; 'tableD', 'alias' =&gt; 'TableD', 'type' =&gt; 'inner', 'conditions' =&gt; 'TableC.tableD_id = TableD.id' ) ) ) ) </code></pre> <p>That is, nesting the joins into the structure. But that doesn't work (CakePHP just ignores the nested <code>'joins'</code> element which was kind of what I expected, but sad.</p> <p>I have seen hints in comments on how to do subqueries (in the <code>where</code> clause) using a statement builder. Can a similar trick be used here?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USDaren Thomas
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to nest joins with CakePHP?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to nest joins with CakePHP?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to nest joins with CakePHP?
      1. USicc97
      1. VTFavorite
    1. COWhy does it have to be nested? Can't you just do the same thing with all the joins at the top level?
      singulars
      1. POHow to nest joins with CakePHP?
      1. USLotus Notes
    2. COI wish I could, but the result is different: I want all the results from the top-level inner join with optional data tacked on from the nested inner join. If I flatten this, then I loose any rows in (TableA inner join TableB) that have no corresponding TableD...
      singulars
      1. POHow to nest joins with CakePHP?
      1. USDaren Thomas
    3. COWow that's pretty complex. I'd probably try and extend find() somehow to add some extra parameters
      singulars
      1. POHow to nest joins with CakePHP?
      1. USDavid Yell
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload