Laravel hasOneThrough(); intermediary table custom column value

I sincerely apologize if this question has been answered before, however I didn't seem to find a similar situation elsewere. If it is a duplicate, please point me to the right direction.

I have three custom tables:

> app_expressions;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| expression | varchar(191)     | NO   |     | NULL    |                |
| bot        | int(10) unsigned | NO   | MUL | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

> app_links;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| source      | varchar(10)      | NO   | MUL | NULL    |                |
| destination | varchar(10)      | NO   | MUL | NULL    |                |
| sid         | int(10) unsigned | NO   | MUL | NULL    |                |
| did         | int(10) unsigned | NO   | MUL | NULL    |                |
| bot         | int(10) unsigned | NO   | MUL | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

> app_replies;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| reply | text             | NO   |     | NULL    |                |
| bot   | int(10) unsigned | NO   | MUL | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

app_expressions is connected to app_replies through app_links


Suppose I have a single record in each table.

  • app_expressions.id is the same as in app_links.sid where app_links.source = 'expression'
  • app_replies.id is the same as in app_links.did where app_links.destination = 'reply'

Using Laravel's hasOneThrough() how can I access app_expressions's reply from app_replies through app_links with condition app_links.destination = 'reply'? Pseudocode below:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Expressions extends Model
{
    /*
    *   Table
    */
    protected $table = 'app_expressions';

    /*
    *   Get all replies linked with this record
    */
    public function get_reply()
    {
        return $this -> hasOneThrough(
            'App\Replies',
            'App\Links',
            'did',  // Destination ID
            'id',  
            'id',
            'sid'   // Source ID
        ) -> where('intermediary table.destination', '=', 'reply') ;
    }
}

I hope I explained it well.

2 answers

  • answered 2019-10-15 16:11 Ahmed Atoui

    in you code when querying you can use WhereHas to filter your table based on Through table filter for example:

    $str='reply';
    $data=Expressions::whereHas('get_reply',function($q) use($str){
    $q->where('app_links.destination',$str);
    })->get();
    

    and don't forgot remove this line on Expressions model :

    -> where('intermediary table.destination', '=', 'reply') ;
    

  • answered 2019-10-15 17:47 Teodor

    Solved it! I had to use pivot table specific methods. Here's the code for reference.

    <?php
    
    namespace App;
    
    use Illuminate\Database\Eloquent\Model;
    
    class Expressions extends Model
    {
        /*
        *   Table
        */
        protected $table = 'app_expressions';
    
        /*
        *   Get all replies linked with this record
        */
        public function get_reply()
        {
            return $this -> belongsToMany(
                                'App\Replies', // The destination table (app_replies)
                                'app_links',  // The pivot table; It can take a Model as argument as well (app_links)
                                'sid',        // Foreign key on pivot table (app_expressions.id on app_links.sid)
                                'did',        // Wanted key on pivot table (app_replies.id on app_links.did)
                                'id',         // Foreign key (app_expressions.id)
                                'id'          // Wanted key (app_replies.id)
                            )         
    
                            -> wherePivot('destination', 'reply'); // app_links.destination = 'reply'
    
        }
    
    

    Philosophy: I'm accessing records from app_replies, through app_links, using app_expressions, if app_links.sid = app_expressions.id and app_links.destination = 'reply'. Tested code for tinker below:

    $app = new App\Expressions;
    
    $expression = $app -> first();
    
    $reply = $expression -> get_reply;
    

    For further information I recommend seeking the parameters of methods declared on \Illuminate\Database\Eloquent\Relations\BelongsToMany\HasRelationship