Category : join

I am trying to get two tables data using join // $participantstation = DB::table(‘participant_station’) // ->join(‘reviews’, ‘participant_station.id’, ‘=’, ‘reviews.participant_station_id’) // ->select(‘reviews.*’, ‘participant_station.*’) // ->where(‘station_id’,$stn->id) // ->where(‘status’, ‘100’) // ; Still some partipant are not linked , that means – participant_station.id’, ‘=’, ‘reviews.participant_station_id if I try this code it only shows the linked participants is there ..

Read more

I have two tables that leads and hts_patients. Both table has phone column and I want to list all leads that has no record on hts_patients table(foreign key is phone). So far, I have tried this and it works but taking so much long time. $countries = [‘DE’,’TR’]; $dateS = Carbon::now()->startOfMonth()->subMonth(4); $dateE = Carbon::now()->startOfMonth(); $leads ..

Read more

I have two tables: leads and hts_patients. Both table has phone column and I want to list all leads that has no record on hts_patients table(foreign key is phone). So far, I have tried this and it works but taking so much long time. $countries = [‘DE’,’TR’]; $dateS = Carbon::now()->startOfMonth()->subMonth(4); $dateE = Carbon::now()->startOfMonth(); $leads = ..

Read more

I’m building an application on Laravel 8.0 where I’m trying to fetch relation which doesn’t exists in joins with DB statement. I’m not getting the desired result I tried using public function ProductList() { $innerQuery = DB::table(‘projects as p’) ->join(‘construction_product_projects as cpp’, ‘p.id’, ‘=’, ‘cpp.project_id’) ->join(‘construction_product as cp’, ‘cpp.construction_product_id’, ‘=’, ‘cp.id’) ->where(‘cp.status’, ‘saved’) ->join(‘project_associate_brand as ..

Read more

When creating my recipes I made it so it makes use of my subcategory, like so $table->unsignedBigInteger(‘subcategories_id’)->nullable(); This can be seen below in the table. class CreateRecipesTable extends Migration { /** * Run the migrations. * * @return void */ public function up() { Schema::create(‘recipes’, function (Blueprint $table) { $table->BigIncrements(‘id’); $table->unsignedBigInteger(‘user_id’); $table->string(‘title’); // a simple ..

Read more

SELECT school_courses.*, schools.school_name, schools.country, schools.city FROM school_courses JOIN schools ON school_courses.school_id = schools.id Hello there, I want to convert the above SQL query to laravel. And also I want to learn joins in laravel query please share any easy step by step tutorial link if you have one. Sourc..

Read more

Hi I am using Laravel 8 and have two tables Products and Categories. Both tables have name column. when i run the following query as join it returns categories.name but not products.name. where am I making mistake. return $product = DB::table(‘categories’) ->join(‘products’, ‘products.category_id’,’=’, ‘categories.id’) ->select(‘products.name’,’categories.name’,’products.price’,’products.description’,’categories.id’,’products.category_id’) ->get(); return view(‘products.single’,compact(‘product’)); Result of the above query is [{“name":"Category ..

Read more

i want to join database with laravel 8, i want that table blog.user will display form users.name code $data = blog::where([ [‘id’, ‘=’, $id], [‘status’, ‘=’, 1], [‘user’, ‘=’, Auth::user()->id] ]) ->join(‘users’, ‘blog.user’, ‘=’, ‘users.name’) ->select(‘blogs.*’) ->get(); error message: SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘id’ in where clause is ambiguous (SQL: select * from ..

Read more

I have a table in laravel which tracks recent views of posts by users. Here is its controller $contents = RecentView::where(‘user_id’, $loggedUser) ->with(‘posts.user’) ->with([‘posts.note’ => function($query) use($loggedUser){ $query->where(‘user_id’, $loggedUser); }]) ->with([‘posts.tags’ => function($query) use($loggedUser){ $query->where(‘user_id’, $loggedUser); }]) ->with([‘posts.savedpost’ => function($query) use($loggedUser){ $query->where(‘user_id’, $loggedUser); }]) ->with([‘following’ => function($query) use($loggedUser){ $query->where(‘user_id’, $loggedUser) ->where(‘follower_id’, ‘id’); }]) ->paginate(12)->toArray(); I ..

Read more

This here is my join controller. Basically there is no error but the problem is that the data is not being shown in the table. The headers from blade.php file are being shown but the data is not being fetched or shown in the table. > My Join Controller Here: <?php namespace AppHttpControllers; use IlluminateHttpRequest; ..

Read more

I’m building an app on Laravel, where I’m having a model named ConstructionProduct something like this: class ConstructionProduct extends Model { protected $table=’construction_product’; public function productDetails() { return $this->hasMany(ConstructionProductDetails::class, ‘construction_product_id’, ‘id’); } } Table construction_product has columns: id, created_at… Then I’ve ConstructionProductDetails model something like this: class ConstructionProductDetails extends Model { protected $table=’construction_product_details’; public function ..

Read more

I’m struggling with implementing SQL query into my php code, the query: select template_id, min(created_at) as created_at from ( select distinct root_template_id as template_id, date_created as created_at, response_id as response_id from db.score inner join db.users ON db.score.supplier_id = db.users.old_id inner join db.acc ON db.acc.user_id = db.users.id where db.acc.account_id = 25) as T group by template_id ..

Read more

I have table inquiries relationship with table user. The pivot table is inquiry_user. I want to select and count inquiry_user as 2 type question and answer. Here is my code. $latest_question = DB::table(‘inquiry_user’) ->where(‘inquiry_user.type’, InquiryUser::TYPE_QUESTION); $latest_answer = DB::table(‘inquiry_user’) ->where(‘inquiry_user.type’, InquiryUser::TYPE_ANSWER); $inquiry = DB::table(‘inquiries’) ->whereNull(‘inquiries.deleted_at’) ->leftJoinSub($latest_question, ‘question’, function ($join){ $join->on(‘inquiries.id’, ‘=’, ‘question.inquiry_id’); }) ->leftJoinSub($latest_answer, ‘answer’, function ..

Read more

I have finally found the solution to sort my table correct. Here is a fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c99d5e43761dfa66503eca8d37bb0617 My SQL statement: SELECT t1.id, t1.`title`,t1.`rank_index` FROM table1 t1 CROSS JOIN ( SELECT MAX(id) maxid FROM table1) t2 ORDER BY IF(rank_index IS NULL, maxid – id + 1 , rank_index – 0.5) ASC I would like to translate this ..

Read more