query help

Something like: https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

Just adapt to DB facade.

Or for full control

use getPdo()

Or convert to eloquent or query builder.

Example:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
          ->select('dc_powners.ownerid', 'dc_powners.oname')->distinct()
          ->selectRaw('count(dc_pets.petid) as countOfPets')
          ->groupby('dc_powners.ownerid')
          ->orderby('dc_powners.oname')
          ->get();

Or use eloquent relations:

https://laravel.com/docs/5.8/eloquent-relationships

@JLRDW - i use getPdo()

but now i have problem query take + 60 second

log

/* Connection to localhost closed at 2019-03-29 12:56:11 */
/* Affected rows: 0  Found rows: 18  Warnings: 0  Duration for 1 query: 00:04:38 */

second

/* Affected rows: 0  Found rows: 18  Warnings: 0  Duration for 1 query: 45.718 sec. */

3rd

/* Connection to localhost closed at 2019-03-29 13:01:59 */
/* Affected rows: 0  Found rows: 18  Warnings: 0  Duration for 1 query: 00:01:38 */

where is the problem localhost or query

How long did it take in codeiginter, are there tens of thousands of records, do you need to query so many, ask questions like that. Look into paginating the results. Etc. I don't know your data, structure, if you indexed properly.

in codeigniter 7 - 9 second is same query i not change anything

$sql = "select teams.name, season_teams.team_id, playl + playv as play,
        (IFNULL(home.won, 0) + IFNULL(vistor.won, 0)) as `won`,
        (IFNULL(home.lost, 0) + IFNULL(vistor.lost, 0)) as `lost`,
        (IFNULL(home.void, 0) + IFNULL(vistor.void, 0)) as `void`,
        (IFNULL(home.goals, 0) + IFNULL(vistor.goals, 0)) as `goals`,
        (IFNULL(home.goalsin, 0) + IFNULL(vistor.goalsin, 0)) as `goalsin`,
        (IFNULL(home.goals, 0) + IFNULL(vistor.goals, 0)) - 
        (IFNULL(home.goalsin, 0) + IFNULL(vistor.goalsin, 0)) as `goalsF`,
        (IFNULL(home.void, 0) + IFNULL(vistor.void, 0)) + 
        (IFNULL(home.won, 0) + IFNULL(vistor.won, 0)) * 3 as `point`
    
        FROM season_teams
        LEFT JOIN(
        SELECT
        localteam_id, time,
        count(localteam_id) as playl,
        SUM(if(localteam_score > visitorteam_score, 1, 0)) as won,
        SUM(if(localteam_score < visitorteam_score, 1, 0)) as lost,
        SUM(if(localteam_score = visitorteam_score, 1, 0)) as void,
        SUM(localteam_score) as goals,
        SUM(visitorteam_score) as goalsin,
        season_id,visitorteam_id, localteam_score, visitorteam_score FROM results
        where `season_id` = {$season_id} 
        GROUP by results.localteam_id
        ) as home on home.localteam_id = season_teams.team_id
        LEFT JOIN(
        SELECT
        localteam_id, time,
        count(localteam_id) as playv,
        SUM(if(localteam_score < visitorteam_score, 1, 0)) as won,
        SUM(if(localteam_score > visitorteam_score, 1, 0)) as lost,
        SUM(if(localteam_score = visitorteam_score, 1, 0)) as void,
        SUM(visitorteam_score) as goals,
        SUM(localteam_score) as goalsin,
        season_id,visitorteam_id, localteam_score, visitorteam_score from results
        where `season_id` = {$season_id}  
        GROUP by results.visitorteam_id
        ) as vistor on vistor.visitorteam_id = season_teams.team_id
        LEFT JOIN teams ON teams.id = season_teams.team_id
        where season_teams.season_id = {$season_id} 
        ORDER BY point DESC, won DESC, void DESC, goals DESC";


        $sth = DB::getPdo()->prepare($sql);
        $sth->execute();
        $quy = $sth->fetchAll(\PDO::FETCH_OBJ);

Is that development or production. Do you have some debug program loaded? Most queries I run in laravel with pagination shows results in the blink of an eye.

I wouldn't think the time would change by that much.

I mean any group by is a little slower, but wow that is a big difference.

Hopefully @cronix or @snapey or @bobbybouwmann will see this and possibly know why this runs so much slower in laravel.

And it's the same query. Meanwhile try db facade, but getPdo() is probably the fastest.

@AdhamLap if no response soon, perhaps start a new post, about speed of codeiginter vs laravel, something like that. Include the two queries in post.

if its a web client, use Laravel debugbar to review the query and duration

@ADHAMLAP - Ever get this figured out. You have me curious why codeigniter is running the same query so much faster than laravel.

yes is more faster in codeigniter table have +1350000 row i try change from innodb to myisam and still slow in laravel

@ADHAMLAP - Like I said, perhaps start a new post and ask about the speed. Refer (link) to this post.

Also if you run that query in php only with pdo, how is the speed? I was hoping someone knew why CI runs that much faster.