Laravel 5.5, DB query join 3 tables, and group by category

orwhere laravel
laravel whereraw
laravel where like
laravel exists
laravel wheredate
laravel latest
eloquent select

I have three tables which I wanna join, and group them by category. something like: "western food" => truck a, truck b currently, if I have two trucks in same category, it doesn't display in result, it just shows one.

tables:
touch_points: id, name

1 | starbucks

2 | kfc

3 | mcdonalds


t_categories: id, name

1 | western food

2 | chinese food

3 | indian food

truck_categories: id, touch_point_id, t_category_id

1 |  1 | 3

2 |  2 | 3

3 |  3 | 4

the query I have now is this:

 $truck = DB::table('truck_categories')
        ->join('touch_points', 'truck_categories.touch_point_id', '=', 'touch_points.id')
        ->join('t_categories', 't_categories.id', '=', 'truck_categories.t_category_id')
        ->select('t_categories.name', 'touch_points.id')
        ->groupBy('t_categories.name')
        ->get();

    return $truck;

Thanks in advance.

you may do like this

$shares = DB::table('shares')
    ->join('users', 'users.id', '=', 'shares.user_id')
    ->join('follows', 'follows.user_id', '=', 'users.id')
    ->groupBy('currency_list')
    ->get();

for more details refer this and this

php, you may do like this $shares = DB::table('shares') ->join('users', 'users.id', '=', '​shares.user_id') ->join('follows', 'follows.user_id', '=', 'users.id')  Laravel Join 3 Tables: Joining more than two tables is very common in any framework, we often need to join 3 or more then three table in Laravel.Here in this tutorial we are going to explain how you can join three tables or more than three tables in Laravel.

try this group by t_category_id

$truck = DB::table('truck_categories')
        ->join('touch_points', 'touch_points.id' , '=', 'truck_categories.touch_point_id')
        ->join('t_categories', 't_categories.id', '=', 'truck_categories.t_category_id')
        ->select('t_categories.name', 'touch_points.id')
        ->groupBy('truck_categories.t_category_id')
        ->get();

    return $truck;  

Database: Query Builder - Laravel, Version. Master, 7.x, 6.x · 5.8 · 5.7 · 5.6 · 5.5 · 5.4 · 5.3 · 5.2 · 5.1 · 5.0 · 4.2 You may use the table method on the DB facade to begin a query. The table method The groupByRaw method may be used to set a raw string as the value of the group by clause: $orders You can even join to multiple tables in a single query: How to use groupby having with DB::raw in Laravel Query Builder? By Hardik Savani | July 6, 2016 | Category : Laravel We most probably require to use group by having because if you work on small project then it's not need to use generally.

If you want to fallow laravel codding standards then you should use ORM like below:

=> For touch_points, t_categories, truck_categories table you have already created Model for this.

=> Then you need to set relationship for related to table parent and child.

=> Related to your table structure you need to add a relation to below:

truck_categories

class TruckCategory extends Eloquent{

  protected $table = 'truck_categories';  

  public function touchpoint()
  {
       return $this->belongsTo('App\TouchPoint'); // touch_points's model name.
  }
  public function tcategory()
  {
       return $this->belongsTo('App\TCategory'); // t_categories model name.
  }
}

touch_points

class TouchPoint extends Eloquent{

  protected $table = 'touch_points';  

  public function truckcategory()
  {
       return $this->hasMany('App\TruckCategory'); // truck_categories's model name.
  }
}

t_categories

class TCategory extends Eloquent{

  protected $table = 't_categories';  

  public function truckcategory()
  {
       return $this->hasMany('App\TruckCategory'); // truck_categories's model name.
  }
}

=> Then data get from all table with group by:

 App\TCategory::with('truckcategory')->get();

Query Builder - Laravel, The database query builder provides a convenient, fluent interface to creating DB::table('users')->chunk(100, function($users) { foreach ($users as $user) { // } })​; Order By, Group By, And Having clause on your joins, you may use the where and orWhere methods on a join. Inserting Multiple Records Into A Table. Laravel's database query builder provides a convenient, fluent interface to creating and running database queries. It can be used to perform most database operations in your application and works on all supported database systems. The Laravel query builder uses PDO parameter binding to protect your application against SQL injection attacks.

Laravel Multiple Table Join Query - very simple way, How to join multiple tables in laravel. this video show join 3 tables in laravel query. http Duration: 19:48 Posted: Mar 25, 2018 in this laravel video tutorial we learn how to apply join or more than 2 tables and we will understand it with very simple example.

Laravel 5.7 tutorial #16 - how to use join on multiple tables, in this laravel video tutorial we learn how to apply join or more than 2 tables and we will Duration: 6:19 Posted: Oct 14, 2018 Laravel Version: 5.5.20 PHP Version: 7.0.15 Database Driver & Version: MySQL 5.5.44 I have two tables, areas and object_areas. I want to get all areas and also add a count of the number of objects per area. areas table have id, name, slu

Eloquent left join group by count issue · Issue #22032 · laravel , Laravel Version: 5.5.20 PHP Version: 7.0.15 Database Driver & Version: MySQL 5.5.44 I have two tables, areas and object_areas. I want to get by areas . id ). If I copy this sql query in the error to HeidiSQL and run it, it works fine. FYI, you can use three backtips for multi-line code blocks instead of one:  Some database statements do not return any value. For these types of operations, you may use the statement method on the DB facade: DB::statement('drop table users'); Listening For Query Events. If you would like to receive each SQL query executed by your application, you may use the listen method. This method is useful for logging queries or

Comments
  • 0 name: "malay" truck : "Starbucks" 1 name: "indian" truck : "donalds" I get this, whereas the malay has two trucks, it just displays one, do i need to use a loop?
  • in t_categories and touch_points model, you sure the relation should be belongsto and not hasmany?
  • yeah better now, as I was working on that. now I have a query: $truck = TCategory::with('truck_categories.touch_points')->get(); json: "id": 7, "name": "indian", "created_at": null, "updated_at": null, "truck_categories": [{ "id": 10, "touch_point_id": 1, "t_category_id": 7, "created_at": null, "updated_at": null, "touch_points": { "id": 1, "brand_id": 1, "user_id": 11, "base_latitude": 3.149099, "base_longitude": 101.762452, "name": "Starbucks",... if there are two trucks in one category it displays fine.
  • now the issue is I know use "where" for touch_points, how do i achieve that as my query is this: $truck = TCategory::with('truck_categories.touch_points')->get();
  • You can try like this: App\TCategory::with(['truckcategory', 'touchpoint' => function($query){ $query->where('<put your code here>'); }])->get(); Thanks,
  • Let us continue this discussion in chat.