Ad

SQL Microsoft Query To Laravel 4.2

- 1 answer

Hello, all from all countries. I need a SQL query converted to LARAVEL 4.2 cause i dont have yet the experience to manage it, my own !

The SQL is in SQL server microsoft 2008

SELECT TOP(10) logst_data, LogSt_date, LogSt_studid FROM Home_LogStudents WHERE logst_action = 225 AND LogSt_sectionID = 257 AND LogSt_date IN (SELECT MIN(LogSt_date) FROM Home_LogStudents WHERE logst_data <> '0' AND logst_action = 225 AND LogSt_sectionID = 257 AND (LogSt_studid IN (select home_id FROM Hw_StudentClasses INNER JOIN Home_Students ON home_studid = Stclass_studid WHERE Stclass_classid = (129))) GROUP BY LogSt_studid) ORDER BY LogSt_data DESC

all i could do is in the NEXT PICTURE :enter image description here but the $id and $class_id are not recognizible in the $query....ALSO THE CODE:

$users = DB::Table('Home_LogStudents')
            ->select('LogSt_data','LogSt_date','LogSt_studid')
            ->where('LogSt_action','=',225)
            ->where('LogSt_sectionID','=',$id)
            ->whereIn('LogSt_date',function($query){
                $query->select('LogSt_date')
                        ->from('Home_LogStudents')
                        ->where('LogSt_data','!=',0)
                        ->where('LogSt_action','=',225)
                        ->where('LogSt_sectionID','=',$id)
                        ->whereIn('LogSt_studid',function($query){
                            $query->select('home_id')
                                    ->from('Hw_StudentClasses')
                                    ->join('Home_Students','Home_Students.home_studid','=','Hw_StudentClasses.Stclass_studid')
                                    ->where('Stclass_classid','=',$class_id);
                            }
                        )
                ->groupBy('LogSt_studid');
                })
        ->orderBy('LogSt_data','DESC')
        ->take(10)
        ->get();

Thank you in advance and only for looking !

Ad

Answer

So i found the answer ,it was easy so all i did was to use Raw Query cause the query was too complex to translate it to laravel or maybe i couldnt do it so i did this.....

$users = DB::select('SELECT     TOP (10) Home_LogStudents.LogSt_data, Home_LogStudents.LogSt_date, Home_LogStudents.LogSt_studid, Home_Students.home_firstname, Home_Students.home_lastname
FROM         Home_LogStudents
INNER JOIN  Home_Students
ON Home_LogStudents.LogSt_studid = Home_Students.home_id
WHERE     (Home_LogStudents.LogSt_action = 225)
AND (Home_LogStudents.LogSt_sectionID = '.$id.')
AND (Home_LogStudents.LogSt_date IN
                          (SELECT     MIN(LogSt_date) AS Expr1
                            FROM          Home_LogStudents AS Home_LogStudents_1
                            WHERE      (LogSt_data <> \'0\')
                            AND (LogSt_action = 225)
                            AND (LogSt_sectionID = '.$id.') AND (LogSt_studid IN
                                                       (SELECT     Home_Students_1.home_id
                                                         FROM          Hw_StudentClasses INNER JOIN
                                                                                Home_Students AS Home_Students_1
                                                         ON Home_Students_1.home_studid = Hw_StudentClasses.Stclass_studid
                                                         WHERE      (Hw_StudentClasses.Stclass_classid = ' .$class_id.')))
                            GROUP BY LogSt_studid))
ORDER BY Home_LogStudents.LogSt_data DESC
');

Actually i used outside the string the $id and the $class_id by using '. $id.' and the '.$class_id.'

I Hope this helps you , so for complex queries... just use RAW QUERIES

Ad
source: stackoverflow.com
Ad