#### 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 ..

Context and Need I’m using an Eloquent model that must be bound to either DB N°1, or DB N°2 according to the execution state. In other words, I need to make it use DB N°1 or N°2 by changing the DB name provided to its QueryBuilder. What I’ve done In my custom model, I’ve written ..

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 ..

I use Bagisto E-Commerce platform (build laravel + vue) and I have created package where I want list categories and filter products by category name. I don’t know how exactly filter products by one view and in bagisto they list products by category page e.g. example.com/category-name I try to use this example, but can’t get ..

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 ..

I have a Model Products that has many of the Model Prices. Every day there is a different price for the product. Now I am trying to create a scope that gives me all products which latest price is between two values. I tried this with a whereHas query: public function scopePriceBetween($query, …$priceRange) { return ..

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: ..

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 ..

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 I use laravel 8. get random 3 users: Users::inRandomOrder()->limit(3)->get(); but I want to add a specific user to the random output. example : Users::find(1); output: // [1, 5, 7, 9] Thanks for helps Sourc.. 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 ..

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 ..

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 ..

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); ..

I have this query using an Eloquent Model, and in some cases i need to use the result of the CASE WHEN in my where clause, depending on the user desire to filter. The user can filter with CODE, NAME or STATUS, this is my query: $query = Users::selectRaw(‘count(Users.Code) as UserCode, Room.Code, Room.Name, count(user_id) as .. 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 I have 3 Tables.1) Recent Views 2) posts and 3) users. i need to join these three tables with intermediate relation. recent table ———— id post_id user_id date posts table ———– id user_id post_title description date users table ———– id username image email date now what is need to get is all data from recent .. 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 ..