skip to Main Content

I have a MariaDB query that I am using in my Laravel application and I would like it to return 7 columns. When I dump the resultant array with PHP it seems to only be returning 4. However when I take the same query and run it in the PhpMyAdmin SQL editor it returns all 7 exactly as I would expect.

Just wondered what might be causing Laravel to filter the results to only four columns, status, name, telephone number and description?

Thanks!

Laravel Query:

    $entries = DB::select('SELECT status, contacts.name, contacts.telephone_number, companies.name, roles.name, stages.description, actions.description FROM entries JOIN contacts ON entries.contact_id = contacts.id JOIN companies ON contacts.id = companies.contact_id JOIN roles ON companies.id = roles.company_id JOIN stages ON roles.id = stages.role_id JOIN actions ON stages.id = actions.stage_id');

Query output in Laravel

Query for PHPMyAdmin SQL Editor:

    SELECT status, contacts.name, contacts.telephone_number, companies.name, roles.name, stages.description, actions.description FROM entries JOIN contacts ON entries.contact_id = contacts.id JOIN companies ON contacts.id = companies.contact_id JOIN roles ON companies.id = roles.company_id JOIN stages ON roles.id = stages.role_id JOIN actions ON stages.id = actions.stage_id

Query output in PHPMyAdmin

2

Answers


  1. it’s the same result in both cases. they just use the same attribute so the result in eloquent override each other. use aliases to fix the issue

    SELECT status, contacts.name as contact_name, contacts.telephone_number, companies.name as company_name, roles.name as role_name , stages.description, actions.description FROM entries JOIN contacts ON entries.contact_id = contacts.id JOIN companies ON contacts.id = companies.contact_id JOIN roles ON companies.id = roles.company_id JOIN stages ON roles.id = stages.role_id JOIN actions ON stages.id = actions.stage_id
    
    Login or Signup to reply.
  2. As @N69S answered, you should set aliases to avoid the override. Another thing tho, if you are using Laravel, eloquent is your friend:

    $entries = DB::table('entries')
        ->join('contacts', 'entries.contact_id', '=', 'contacts.id')
        ->join('companies', 'contacts.id', '=', 'companies.contact_id')
        ->join('roles', 'companies.id', '=', 'roles.company_id')
        ->join('stages', 'roles.id', '=', 'stages.role_id')
        ->join('actions', 'stages.id', '=', 'actions.stage_id')
        ->select('status', 'contacts.name AS contact_name', 'contacts.telephone_number', 'companies.name AS company_name', 'roles.name AS role_name', 'stages.description AS stage_description', 'actions.description AS action_description')
        ->get();
    

    A better approach will be using models and set relations.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search