0

I have this PHP raw query that I would want to convert to Eloquent.

SELECT telcos.id AS telco_id, telcos.name AS telco_name, telco_prefixs.prefix AS prefix, services.name AS service_name, services.id AS service_id, service_credentials.credentials AS credentials FROM telcos LEFT JOIN telco_prefixs ON telco_prefixs.telco_id = telcos.id , services LEFT JOIN service_credentials ON service_credentials.service_id = services.id

The query works perfectly on MySQL DB but I would want to convert it to laravel to consume in my application.

I have tried this but something is off. 

$q = Telco::leftJoin('telco_prefixs', 'telco_prefixs.telco_id', '=', 'telcos.id , services');
$q->leftJoin('service_credentials', 'service_credentials.service_id', '=', 'services.id');
$q->select(['telcos.id AS telco_id as telcos.id AS telco_id','telcos.name AS telco_name as telcos.name AS telco_name','telco_prefixs.prefix AS prefix as telco_prefixs.prefix AS prefix','services.name AS service_name as services.name AS service_name','services.id AS service_id as services.id AS service_id','service_credentials.credentials AS credentials as service_credentials.credentials AS credentials']);
$q->get();

For some reason I get PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'services.name' in 'field list'

I am not sure why and yet the relationship is defined.

Anyone help?

Thank you

  • 1
    `'services.name AS service_name as services.name AS service_name'` – Vinay Feb 09 '20 at 04:27
  • 2
    You're not joining the services table on which is what I imagine will be causing your issue. If you're wanting this to be more "Eloquent" I would suggest looking at using relationships rather than joins. – Rwd Feb 09 '20 at 09:30
  • There's duplicate values in your `select` clause as mentioned by @Viney. You need to have a `join` clause for `services` table, only then can you use `services` with `service_credentials` in a `join` clause. If this is the case, please refactor your code to reflect these changes. – Kanwarbir Singh Feb 26 '20 at 22:05

0 Answers0