Pass MySQL query to Eloquent

Question

Pass MySQL query to Eloquent

Navigation

#1 by (0 votes)

0

Good I would like to pass a query to Eloquent, but it does not work for me. I think it's because of the subquery, but come on, I do not know. Greetings and thanks in advance

Here I leave my Eloquent query which is not well done:

public function getDatas (){
    $connection = $this->db->getDatabaseManager();

    $subQuery = $this->db->connection('mirror')->table('bbdd.table5 dic')
                    ->where('dic.company','=','XXXXX')
                    ->whereRaw('date(dic.date_creation) >= date_add(curdate(), INTERVAL -5 DAY)')
                    ->whereRaw('date(dic.date_creation) < curdate()');

    $datas = $this->db->connection('mirror')->table('bbdd.table1 as din')
                    ->join('bbdd.table2 as dcn','dcn.id_algo','=','din.id_algo')
                    ->join('bbdd.table2 as dcn','dcn.id_otro','=','din.id_otro')
                    ->join('bbdd.table3 as ds','ds.id_algo','=','din.id_algo')
                    ->join('bbdd.table4 as dsn','and dsn.id','=','din.status')
                    ->whereIn('din.id_algo',$subQuery)
                    ->orderBy('din.dato3','ASC')
                    ->orderBy('ds.dato5','ASC')
                    ->select('din.dato1, din.dato2, din.dato3, dcn.dato4, ds.dato5, ds.dato6, dsn.dato7')
                    ->get()
                    ->map(function ($item, $key) {
                        return (array) $item;
                    })
                    ->all();
    return $datas;
}

Mysql Consultation

SELECT din.dato1, din.dato2, din.dato3, dcn.dato4, ds.dato5, ds.dato6, dsn.dato7
        FROM
            bbdd.table1 as din,
            bbdd.table2 as dcn,
            bbdd.table3 as ds,
            bbdd.table4 as dsn       
        WHERE  
            din.id_algo in (
                           select dic.id_algo
                           FROM bbdd.table5 dic
                           where dic.company='XXXXX'
                           and date(dic.date_creation) >= date_add(curdate(), INTERVAL -5 DAY)
                           and date(dic.date_creation) < curdate()
            )
            and dcn.id_algo = din.id_algo
            and dcn.id_otro = din.id_otro
            and ds.id_algo = din.id_algo
            and dsn.id = din.status
        order by din.dato3 asc, ds.dato5 asc

mysql eloquent

asked by TaGy 17.10.2017 в 19:52

source

1 answer

Upload files to an FTP with PowerShell How can I pass data from an activity to a GoogleMap.InfoWindowAdapter?

score 0 · Answer 1

Try this, if something fails it shows that it says the error

public function getDatas (){
    $connection = $this->db->getDatabaseManager();

    $datas = $this->db->connection('mirror')->table('bbdd.table1 as din')
                    ->join('bbdd.table2 as dcn','dcn.id_algo','=','din.id_algo')
                    ->join('bbdd.table2 as dcn','dcn.id_otro','=','din.id_otro')
                    ->join('bbdd.table3 as ds','ds.id_algo','=','din.id_algo')
                    ->join('bbdd.table4 as dsn','and dsn.id','=','din.status')
                    ->whereIn('din.id_algo',function ($query) {
                                $query->$this->db->connection('mirror')->table('bbdd.table5 dic')
                               ->where('dic.company','=','XXXXX')
                               ->whereRaw('date(dic.date_creation) >= date_add(curdate(), INTERVAL -5 DAY)')
                               ->whereRaw('date(dic.date_creation) < curdate()')->get();
                      })
                    ->orderBy('din.dato3','ASC')
                    ->orderBy('ds.dato5','ASC')
                    ->select('din.dato1, din.dato2, din.dato3, dcn.dato4, ds.dato5, ds.dato6, dsn.dato7')
                    ->get();
    return $datas;
}