Category : laravel-query-builder

I try to show the ‘categories’ from Many to Many relationships and use whereNotNull at the same time but when I use whereNotNull to check that the users have approved then the categories from the relationships are disappeared in the blade view. If I have not put whereNotNull. The list of users who are vendors ..

Read more

I have following query: SELECT cd_4.userfield, COUNT( DISTINCT (Case When cd_4.disposition_Last = ‘ANSWERED’ Then cd_4.uniqueid ELSE NULL END) ) AS Answered_Count, COUNT( DISTINCT (Case When cd_4.disposition_Last <> ‘ANSWERED’ Then cd_4.uniqueid ELSE NULL END) ) AS Not_Answered_Count FROM (SELECT cd_2.Userfield, cd_2.Start_Date, cd_2.uniqueid, cd_2.Row_Id_Max, cd_3.disposition AS disposition_Last FROM (SELECT cd_1.userfield, cd_1.Start_Date, cd_1.uniqueid, MAX(cd_1.Row_Id) AS Row_Id_Max FROM cdrnew ..

Read more

I have a collection Products and productVendors: ["ASICS TIGER","FLEX FIT"] productTypes: ["SHOES","T-SHIRTS"] productTags: ["autumn","cap"] I can easily write: $products->whereIn(‘productVendors’, ["ASICS TIGER","FLEX FIT"]) ->whereIn(‘productTypes’, ["SHOES","T-SHIRTS"]) ->whereIn(‘productTags’, ["autumn","cap"])->get(); but thats not what I need. I need to get all products but like this ->whereIn(‘productVendors’, ["ASICS TIGER","FLEX FIT"]) OR ->whereIn(‘productTypes’, ["SHOES","T-SHIRTS"]) OR ->whereIn(‘productTags’, ["autumn","cap"])->get(); It Will be perfect ..

Read more

I use laravel-nestedset for creating the category of articles. Laravel: 8 / laravel-nestedset: 5.0.3 Table categories : Schema::create(‘categories’, function (Blueprint $table) { $table->increments(‘id’); NestedSet::columns($table); $table->boolean(‘publish’)->default(true); … Table articles: Schema::create(‘articles’, function (Blueprint $table) { $table->increments(‘id’); $table->integer(‘category_id’)->unsigned()->nullable(); $table->foreign(‘category_id’)->references(‘id’)->on(‘categories’); … I have created a branch and a sub-branch in the category table like this: news > politics Also, ..

Read more

I need to filter products based on search terms, so I have this in my controller: $activeFilters = Array( "Display dimensions" => Array("5′ inches","5.5′ inches"), "Color" => Array("Grey","Black") ); $product = Product->whereHas("attributes", function($query) use ($activeFilters){ foreach($activeFilters as $group){ $query = $query->whereIn("product_features.text", $group); } })->get(); Now if I set inside active filters array only one group ..

Read more

I am having issue to apply groupBy(‘dates) on activity_planners nested object of collection. AnyOne Can help? $this->with([ ‘customizeOutcomes’ => function($query) { $query->with([ ‘customizeOutputs’ => function($q) { $q->with([ ‘projectOutputs’ => function($qr) { $qr->with([‘output’, ‘projectOutputIndicators’ => function($qi){ $qi->with([‘outputIndicator’]); }, ‘projectActivities’ => function($qa) { $qa->with([‘activityPlanners’ => function($aq){ $aq->orderBy(‘dates’); }])->orderBy(‘position’); }]); }]); }]); } ])->find($id); The output: customize_outcomes: customize_outputs: ..

Read more

I am running the following query using the search() function below – the problem is I need to group the where clauses – what am I doing wrong? select `standings`.*, `users`.`name` as `user` from `standings` left join `users` on `standings`.`user_id` = `users`.`id` where `users`.`name` like ‘%bob%’ or `users`.`email` like ‘%bob%’ and `standings`.`tenant_id` = ‘1’ In ..

Read more

I have three tables. Country: id, country_name State: id, country_id, state_name City: id, state_id, city_name I want to get a specific country ($id = 1) with all the cities in every state. I try this code. DB::table(‘country as a’) ->join(‘state as b’, ‘b.country_id’, ‘=’, ‘a.id’) ->join(‘city as c’, ‘c.state_id’, ‘=’, ‘b.id’) ->select(‘a.*’, ‘b.*’, ‘c.*’) ->where(‘a.id’, ..

Read more

I have a query and it works fine, but I need to enhance this query. the first problem is I can’t groupBy will not return all the meals to count it. the map method is to count the meals and I think it smiley code, so any suggestions. Shift::leftJoin(‘mt_plans’, ‘mt_plans.shift_id’, ‘=’, ‘shifts.id’) ->leftJoin(‘subscriptions’, ‘subscriptions.plan_id’, ‘=’, ..

Read more

In my Laravel query builder, I have this part: ->leftJoin(‘tbluserprivileges’, function ($join) use ($id) { $join->on(‘tblprivileges.id’, ‘=’, ‘tbluserprivileges.PrivilegeID’); $join->on(‘tbluserprivileges.UserID’, ‘=’, (int)$id); }) I wanted to have two conditions on a LEFT JOIN with the other parameters to be dynamic, which is the user ID. However the SQL output is select `tblprivileges`.`id`, `tblprivileges`.`Description`, `tbluserprivileges`.`UserID` from `tblprivileges` ..

Read more

I am using Yajra datatable. I want to display data and filter data with the employee name I have 3 tables 1- employees= id, name, age. 2- Allowance= id, name. 3- employee_Allowance= employee_id , Allowance_id, amount How to be the data table class query? enter code here want table return column {employee , allowance name ..

Read more

I have 3 tables ‘cars‘, ‘options‘ and a bridge table ‘car_option‘ linking between these two tables. I’m having issue to retrieve "cars" data of selected car_option by user. So far I have tried this. $carnew = DB::table(‘car_option’) ->join(‘cars’, ‘cars.id’, ‘=’, ‘car_option.car_id’) ->whereIn(‘car_option.option_id’ , $option_ids); here $option_ids return an array of selected options by the user. ..

Read more

I’m searching to load a relation inside a pivot table with "->with()". Table structure: Are 5 tables and Laravel Eloquent models user (id) team (id) role (id) team_user (id, team_id, user_id) team_user_role (id, team_user_id, role_id) class User extends Model { function teams(){ return $this->belongsToMany(Team::class) ->using(TeamUser::class); } } class Team extends Model { function users(){ return ..

Read more

I’m searching to load a relation inside a pivot table with "->with()". Table structure: Are 5 tables and Laravel Eloquent models user (id) team (id) role (id) team_user (id, team_id, user_id) team_user_role (id, team_user_id, role_id) class User extends Model { function teams(){ return $this->belongsToMany(Team::class) ->using(TeamUser::class); } } class Team extends Model { function users(){ return ..

Read more

I have a problem with some conflict in this query (whole query on bottom) It was working fine before I have added that part : ->join(‘ports’, ‘items.ports_id’, ‘ports.id’) ->join(‘cities’, ‘ports.cities_id’, ‘cities.id’) ->join(‘regions’, ‘cities.regions_id’, ‘regions.id’) ->join(‘countries’, ‘regions.countries_id’, ‘countries.id’) // works WHEN $place selected ->when($place, function($query) use ($place) { $query->where(‘ports.name’, $place) ->orWhere(‘cities.name’, $place) ->orWhere(‘regions.name’, $place) ->orWhere(‘countries.name’, $place); ..

Read more

I have following code. the result i get is a single array. is there any way i can make it a nested array? $contents = RecentView::where(‘recent_views.user_id’, $loggedUser) ->leftJoin(‘feed_posts’,’recent_views.post_id’,’=’,’feed_posts.id’) ->leftJoin(‘users’,’feed_posts.user_id’,’=’,’users.id’) ->paginate(12)->toArray(); and the result is: [id] => 71 [user_id] => 71 [post_id] => 529 [site_id] => 103 [date_time] => 2020-11-05 00:00:00 [title] => Lorem Ipsum [description] ..

Read more

The following query is returning the above error: $readings = MeterReading::where(‘meters_readings.meter_id’, $m->meter_id) ->where(‘meters_readings.read_local_at’, ‘>=’, function($query) use ($m) { $query->selectRaw(‘MAX(flow_readings.read_local_at)’) ->from(‘flow_readings’) ->where(‘flow_readings.meter_id’, $m->meter_id) ->where(‘flow_readings.read_local_at’, ‘<=’, ‘meters_readings.read_local_at’); })->orderBy(‘meters_readings.read_local_at’, ‘DESC’) ->first(); The problem seems to be the subquery timestamp comparison. It complains that the parent table timestamp is of invalid input syntax. If I replace it with a ..

Read more