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 ..
Category : laravel-query-builder
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 ..
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, ..
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’, ..
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’, ‘=’, ..
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..
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. ..
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 ..
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); ..
my table1 : id name 11 a 12 b 13 c 14 d my table2 : id table1_id price created_at 1 11 10 2020-11-19 2 11 12 2020-11-20 3 11 14 2020-11-21 4 11 16 2020-11-22 2 12 10 2020-11-16 3 12 12 2020-11-17 4 12 14 2020-11-18 2 13 16 2020-11-19 3 13 18 ..
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 ..
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] ..
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 ..
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 ..
I have some query to postgre like this: SELECT date::date FROM unnest(ARRAY[‘2020-10-31′,’2020-10-30′,’2020-10-29′,’2020-10-28’]) as date How I can do it use laravel query builder ? Thanks in advance for your help) Sourc..