I have a projects table with the value of the following column:

id_project   name           website         country_id  currency_id  timezone_id
==========.  ====           =======         ==========. ===========. ===========
1            project name.  www.yahoo.com.  10          8            10

and a countries table with the value of the following column:

id_country.      country_name
==========.      ============
10               America

and a currencies table with the value of the following column:

id_currency      currency_name
===========      ============
8                BD

and a timezones table with the value of the following column:

id_timezone      time_onze    country_name
===========      =========    ============
10               Asia/Dhaka   Bangladesh

Now, I want to run a query that should give me name, website, country_name, time_zone, currency_name. BUT you can see the country_name is exist on both table which is countries and timezones. So, the query should give me the country_name from countries table NOT time_zones table.

So, for that I am running following query:

$projects = DB::table('projects')
->leftJoin('countries', 'projects.country_id', '=', 'countries.id_country')
->leftJoin('currencies', 'projects.currency_id', '=', 'currencies.id_currency')
->leftJoin('timezones', 'projects.timezone_id', '=', 'timezones.id_timezone')
->get(['name', 'website', 'code', 'projects.currency_name', 'time_zone']);

return response()->json($projects, 200);

But Can't get the data

Error message:

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'projects.currency_name' in 'field list' (SQL: select name, website, code, projects.currency_name, time_zone from projects left join countries on projects.country_id = countries.id_country left join currencies on projects.currency_id = currencies.id_currency left join timezones on projects.timezone_id = timezones.id_timezone)"

CodePudding user response：

Try this

$projects = DB::table('projects')
->leftJoin('countries', 'projects.country_id', '=', 'countries.id_country')
->leftJoin('currencies', 'projects.currency_id', '=', 'currencies.id_currency')
->leftJoin('timezones', 'projects.timezone_id', '=', 'timezones.id_timezone')
->get(['projects.name', 'projects.website', 'currencies.currency_name', 'timezones.time_zone']);

return response()->json($projects, 200);

or with select helper

$projects = DB::table('projects')
->leftJoin('countries', 'projects.country_id', '=', 'countries.id_country')
->leftJoin('currencies', 'projects.currency_id', '=', 'currencies.id_currency')
->leftJoin('timezones', 'projects.timezone_id', '=', 'timezones.id_timezone')
->select(
['projects.name', 'projects.website', 'currencies.currency_name', 'timezones.time_zone']
)
->get();

return response()->json($projects, 200);

There is no code column in your DB. So i dont include it. Sorry for my bad english

Hope this help you

CodePudding user response：

Actually you are doing wrong query

$projects = DB::table('projects')
->leftJoin('countries', 'projects.country_id', '=', 'countries.id_country')
->leftJoin('currencies', 'projects.currency_id', '=', 'currencies.id_currency')
->leftJoin('timezones', 'projects.timezone_id', '=', 'timezones.id_timezone')
->get(['projects.name', 'projects.website, 'currencies.currency_name', 'timezones.time_zone']);

return response()->json($projects, 200);

I don't see any code column can you make sure where it's comes from.

CodePudding user response：

There's an error in your column selection, as all the column names are unique you can totally ignore adding the table name in front of columns.

$projects = DB::table('projects')
->leftJoin('countries', 'projects.country_id', '=', 'countries.id_country')
->leftJoin('currencies', 'projects.currency_id', '=', 'currencies.id_currency')
->leftJoin('timezones', 'projects.timezone_id', '=', 'timezones.id_timezone')
->get(['name', 'website', 'currency_name', 'time_zone']);

return response()->json($projects, 200);