SQL join in Laravel, how to?

Question

Help me please, with JOINS, learning SQL and joins are painful :(

I have table: categories with column name: Name, ID

I have table: sub_categories with column name: ID

I have table: provider_services with column name: category_id & subcategory_id

When I do in laravel view blade {{$provider->providerServices()->where("status",1)->count()}}

I will receive: 2

It means, that provider, has 2 services with category_id 2, 3 and subcategory_id 4,8

Question: How can I join this data to table categories and grab the category Name using cat_id & subcat_id?

Category_id 2 is - Painting
Category_id 3 is - Plumbing
SubCategory_id is - Aqua
SubCategory_id 8 is - Test

So, as A result I need to have something: Painting and Aqua, Plumbing and Test

Thank you very much!

Collin · Accepted Answer · 2020-02-20 10:50:27Z

1

Inner Join Clause

The query builder may also be used to write join statements. To perform a basic "inner join", you may use the join method on a query builder instance. The first argument passed to the join method is the name of the table you need to join to, while the remaining arguments specify the column constraints for the join. You can even join to multiple tables in a single query:

$users = DB::table('users')
            ->join('contacts', 'users.id', '=', 'contacts.user_id')
            ->join('orders', 'users.id', '=', 'orders.user_id')
            ->select('users.*', 'contacts.phone', 'orders.price')
            ->get();

Source: https://laravel.com/docs/5.8/queries#joins

The above example can easily be changed to work for you. The join function for is made the following:

->join('table', 'column1', '=', 'column2')

answered Feb 20, 2020 at 10:50

Collin

9841 gold badge10 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

EasyJob Meistars · Accepted Answer · 2020-02-20 12:32:11Z

0

Strange, why this SQL query, return overall status of Categories & Sub-Categories, but not status of provider_services?

SELECT categories.name, categories.id, sub_categories.id as idPodKa, sub_categories.name as ImjaPodkategorij, provider_services.status as Status
FROM categories
LEFT JOIN sub_categories 
ON categories.id=sub_categories.category_id 
LEFT JOIN provider_services
ON categories.id=sub_categories.category_id=provider_services.status
where provider_services.status = 1

answered Feb 20, 2020 at 12:32

EasyJob Meistars

235 bronze badges

Collectives™ on Stack Overflow

SQL join in Laravel, how to?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related