Query reports and group by user and count certain field

I have this report migration $table->unsignedInteger('user_id')->unsigned()->nullable()->index(); $table->string('reason'); // can be one of three values, say ('bad, 'good', 'something') i want now to display a table where i have the user and column for each reason counting how many times he got records on this user | bad | good | something Jeffrey | 3 | 10 | 2

Maged's avatar
Maged's avatar MagedLaravel
3

WithCount and WithAvg both in a query

How can i use withCount and withAvg both inside a query? I want reviews count and avg for all users.

iamarbabhussain's avatar
iamarbabhussain's avatar iamarbabhu...Eloquent
7

MySQL to Laravel Query

select a.members, count() as teams from(SELECT count() as members FROM team_member_detail group by team_id) as a group by a.members this query i have as converted to Laravel query: $data = DB::query()->select(DB::raw('a.members, count() as teams'))->fromSub(function ($query) { $query->select(DB::raw('count() as members'))->from('team_member_detail') -

eyantra's avatar
eyantra's avatar eyantraEloquent
10

Need help with a difficult query

I have an app with Users and Jobs. Each User can have many Jobs. Each Job has a start_date and an end_date. I want to create a filter that gets a list of users who have a min of X years of experience and a max of Y years of experience. Years of Experience is typically calculated by getting the difference of start_date and end_date of each job of a user, however, there's a catch

mrkarma4ya's avatar
mrkarma4ya's avatar teos_97Laravel
2

Reset Password Link returning query string as opposed to "pretty url"

I'm looking to implement some "Forgot Password" functionality to an existing app and looking through the docs at https://laravel.com/docs/8.x/passwords. I'm able to generate a password reset link and redirect the user back to the site however the token needed to reset the password is contained within a query string and not part of the url. I.e. myssite.com/password-re

Smiffy's avatar
Smiffy's avatar SmiffyLaravel
3

Improving this eloquent query ?

Hi, Here is the context, employees have worksheets. When worksheets are completed, every month the admin will make a recap document to pay them. I'd like to have a dashboard with only employees that need to be paid (and with some informations about the amount etc). Here is the Eloquent query I've made, it works but I'm not sure if it's the best solution here. $employees = Emplo

Ashraam's avatar
Ashraam's avatar AshraamEloquent
0

Dynamic Query

hi, i have this query SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'max(case when tahun = ''', tahun, ''' then target end) tahun' ) ) INTO @sql FROM renstra_indikator; SET @sql = CONCAT('SELECT b.start_periode,b.end_periode,d.desc_renstra tujuan, e.desc_renstra sasaran,c.desc_renstra indikator, f.desc_satuan satuan, ',

sitinj's avatar
sitinj's avatar sitinjLaravel
3

Need help with a query builder scope

I have 3 tables -> scripts -> guidelines -> calls a script can have multiple guidelines, and a guideline can have multiple calls. in my scripts table I have a active_call column, which is a foreign key corresponding to the current call. I need to build a scope where I filter scripts in which their active call is the first call of their guideline. so I basically need to

bellini's avatar
bellini's avatar belliniCode Review
2

Inline query scope with parameter

How can I pass a parameter to an inline query scope? $shows = Show::upcoming()->with( ['events' => function($q, $reward) { $q->restrictDays($reward->ticket_restrictions); }] )->get(); This causes the following error: Too few arguments to function App\Http\Controllers\EventController::App\Http\Controllers{closure}(), 1 passed i

MrMoto9000's avatar
MrMoto9000's avatar tykusEloquent
2

Advanced query scope

I was hoping to write an advanced query scope that would return items based on a flexible number of parameters. For example, accepting a JSON string that could have any number of days of the week inside it to be excluded from a query: public function scopeRestrictDays($query, String $restrictedDays = null) { $restrictedDays = json_decode($restrictedDays)->day ?? [];

MrMoto9000's avatar
MrMoto9000's avatar martinbeanEloquent
2

Custom withSum query

Hello, Can a query like the one below be written using withSum method or in a more eloquent way? DB::raw('(select sum(JSON_LENGTH(`stores`.`baskets`)) from `stores` where `users`.`id` = `stores`.`user_id`) as `baskets_count`')

Eldair's avatar
Eldair's avatar EldairEloquent
0

Query builder delete query questions

Below is a working query that I am trying to recreate using the query builder in Laravel so that I can utilize the query bindings: $query = 'delete b.* from locations_hierarchy as a join locations_hierarchy as b on b.descendent_id = a.descendent_id and b.id <> a.id where b.depth > 0 and a.ancestor_id in (' . implode(',', $existingSublocations) . ')'; $results = DB::

TylerN86's avatar
TylerN86's avatar TylerN86Laravel
0

Filters problem eloquent model query

Hello guys i have a frontend form with dropdown and fulltext search, if user don't set any filters the index method of controller return all data otherwise return data that match with filters. The filters are passed directly in url using "GET" I get data using model scope from mysql database and at the moment i merge all query using unionAll but the filters stop worki

manuelbosi's avatar
manuelbosi's avatar manuelbosiLaravel
0

Best way to combine query builder results?

Hi All, I am breaking down an old SQL View that summarizes a transaction table for clients across different date ranges. Ive recreated these smaller views into query builder laravel queries, and now i need to combine them (multi-dimensional array with date ranges as keys, then client name/sum as k => v pair) i know i can do this with a foreach - but wondering in general if t

TuffRivers's avatar
TuffRivers's avatar TuffRiversLaravel
2

Horizon cause an invalid SQL query (Syntax error: 7 ERROR: zero-length delimited identifier at or near)

Horizon Version: 5.7.10 Laravel Version: 8.53.1 PHP Version: 8.0.9 (Sail) Redis Driver & Version: predis/phpredis / Database Driver & Version: Version shipped with PHP 8.0.9 Description: Hello, I have a small problem. I just discovered the job system of Laravel, so I need to trigger an HTTP request regularly from the moment the user connects to a particular page. I

SirMishaa's avatar
SirMishaa's avatar AntibilanLaravel
2

Need help in laravel eloquent query to get all the job_id with additional status.

Table: job_table ---------------------------------------------------------------------------- id | jobcode | updated_at | created_at ---------------------------------------------------------------------------- 1 | x123 | 2020/11/03 |2020/11/03 2 | x124 | 2020/11/03 |2020/11/03 3 | x125 | 2020/11/03 |2020/11/03 4 | x126 | 2020/11/03 |2020/11/03 5 | x

princeparaste's avatar
princeparaste's avatar SnapeyEloquent
1

Laravel join query get second table data in a object

I am getting my data using inner join, but it's just appending second table's data in first ones, like this: "hadithNumber": 20, "hadithEnglish": "", "chapterId": 9, "bookSlug": "al-tirmidhi", "volume": 0, "status": "Sahih", // Second table data "chapterNumber&qu

zainshabir00's avatar
zainshabir00's avatar zainshabir...Laravel
2

Laravel with query relationship not working in JSON response API

I have 3 tables named books, chapters & hadiths. These tables are connected. When i try to directly access chapter relationship in my view balde file $hadiths foreach loop like this $hadith->chapter, it gives me right data of chapter which belongs to hadith. But when i try to get relation in my Hadith query using with relation to get hadith's chapter in JSON response, th

zainshabir00's avatar
zainshabir00's avatar zainshabir...Laravel
7

Need a Laravel Left Join query to find out wheather the Job is assigned or not to user (with some conditions))

Table 1 Name: job_table https://imgur.com/TNQBICS Table 2 Name: assign_Table https://imgur.com/ecaCd3P I want a laravel query where i want to left join job_table with assign_table and CASE 1 : if the job is assigned in the assign_table and have user_id and assign_date not null then i want the result to show job_code, user_id and assign_date Otherwise CASE 2: if the job_code

princeparaste's avatar
princeparaste's avatar SilenceBri...Eloquent
1

Help with query for parent and child

When searching replies if the reply is a child its parent should also be returned. (Not as a relationship) And what I naively think should work... Reply::where('body', 'like', '%' . 'the search term' . '%') orWhere(function ($query) { $query->select('id') ->from('replies as parent') ->whereColumn('parent.id', 'replies.parent_id') })->get();

webrobert's avatar
webrobert's avatar SilenceBri...Laravel
7

how to query this

Hi ,I have two table users and tags. I want to get all user that has at one least tag. tags table user_id tag 1 php 1 laravel 2 php controller $tags = $request->get('tags'); on dd($tags) I get array:1 [▼ 0 => "laravel,php,js" ] My query $users = User::where('status',1); $users->when($tags, function ($query, $

david001's avatar
david001's avatar MichalOrav...Laravel
9

Make calculations in Elequent Query

Hey! 👋🏼 I have an Eloquent query where I need to add calculations in it to sort by. In this case, I need to add successful_requests * 100 / total_requests as an additional attribute, is there any way how that can be done in Eloquent and not using DB::raw? I know that withCount runs as a sub-query, that's why those values like total_requests are not accessible $query = D

Reinis.lucis's avatar
Reinis.lucis's avatar Reinis.luc...Eloquent
1

How to process the resultset of a raw query in Laravel

I have a helper function which takes category as input and returns with all items for that category in comma separated string. public static function getItemsByCategory($categoryId){ $categoryString=""; $query = DB::table('items'); $query->select(DB::raw('item_code')) ->where('category', '=', $categoryId)->get(); f

gse.amit's avatar
gse.amit's avatar gse.amitEloquent
4

Raw DB query but with Eloquent

At the moment I have a model called contacts and I'm displaying a list of contacts like this: $contacts = Contacts::orderBy('first_name')->orderBy('last_name')->paginate(200); return view('contacts/index', ['contacts' => $contacts]); Now I want to list all contacts which have duplicates, but utilising the same view. So ideally I want to do it via Eloquent rather tha

Garet's avatar
Garet's avatar GaretLaravel
4

Mysql update timestamp in multi rows query

Hello Guys, I have about 600 rows that contain the same date but different timestamps (Hour, min, second ). When I run this query, I find all the ones I need to update SELECT * FROM `message` where DATE(`date_created`) = DATE('2021-06-06') I would like to update all of the rows with a new time stamp like 2021-05-22. I tried this query but didn't work UPDATE `message` SET `dat

dk4210's avatar
dk4210's avatar Tray2General
1

Count Query in Laravel taking to long to respond. How to Optimize it ?

Hi, In my database i have like 10-20 companies only but having 7,000 - 8,000 rows in other tables like table1,2,3,4. When i run my query of count it just take too long to respond and my page doesn't even load. Its gives me error Maximum execution time of 60 seconds exceeded How can i optimized my query to run faster and dont give me error ? $company = Companies::select('compa

princeparaste's avatar
princeparaste's avatar princepara...Eloquent
5

Called 'diff' on Laravel collection, but could have been retrieved as a query

Hi all, I am running Larastan for static analysis and my code retrieves some results from the database via Eloquent and uses the diff() method to retrieve the items not in the collection passed, it works fine but Larastan reports this error: Called 'diff' on Laravel collection, but could have been retrieved as a query. What would be the alternative? My code: FlightRoute::all()

bosborne's avatar
bosborne's avatar bosborneLaravel
2

Can anybody help me to build its query builder?

lets say, Book Model has Many-to-Many relationship with Tag model Lets assume, there are 5 tags, of ID 1,2,3,4,5 Now I want to find the books, which is related to tag ID 3. What currently I am doing: $id = 3; $related_books = []; $all_books = Book::all(); foreach($all_books as $book) { if($book->tags->pluck('id')->contains($id)) { array_push($related_books, $book

dgvai's avatar
dgvai's avatar dgvaiLaravel
2

Add relationship to eloquent query to model with scopes

I have this code in my model, but then using eloquent if I add a with('parent') the query stops working. Important side note this is not my code. public function parent() { return $this->belongsTo(Category::class, 'category_id', 'id'); } function scopeName($query, $request) { if ($request['query'] && $request["query"][&q

eggplantSword's avatar
eggplantSword's avatar orestLaravel
2

How to write Eager Loading in Query Laravel

$query->with(['batches'])->select(['name'])->orderBy('name', 'asc')->limit(1); Result: "batches": [ { "id": 3079, "cost": 100, "stock": 20 }, { "id": 3080, "cost": 100, "stock": 20 } ] I am getting the batches for the relationship with Products Model. But f

nafeeur10's avatar
nafeeur10's avatar Sergiu17Laravel
1

Query model's attribute that is a accessor

Hello everybody, I got some custom accessor on my model like this: public function getRandomAttribute(): ?string { return "Test"; } Now I'd like to query this randomAttribute: Item::whereRandom("Test")->first() This does not work - of course, as Eloquent is querying the database and this value is not existing in my database. So I just wondered, if:

ahoi's avatar
ahoi's avatar SilenceBri...Laravel
2

updateOrCreate query just based on primary id ? can we do it like this and is this a good practice ?

can we run updateOrCreate query just based on primary id ? I just want to know if this is a good practice in laravel or not ? $p_contact = Contact::updateOrCreate( ['id' => $contact_id], [ 'company_name' => $request->producer['company_name'], 'address' => $request->producer['address'], 'city' => $r

princeparaste's avatar
princeparaste's avatar bugsyshaLaravel
9

leftJoin query wraps string into backticks

I'm facing an odd error when using a leftJoin query. $status = 'active'; $active_ids = \App\User::leftJoin('user_profiles', function($join) use ($status) { $join->on('user_profiles.user_id', '=', 'users.id') ->on('user_profiles.status', '=', $status); }) ->where('type', 'client') ->get(); I get

Enea74's avatar
Enea74's avatar Enea74Eloquent
2

get value from query as integer

How do i get the present_count value from the query below as an integer? $attendance = Attendance::selectRaw('count(present) as present_count') ->whereMonth('date', $month) ->whereYear('date', $year) ->where('staff_id', $request->input('user_id')) ->get()->toArray();

tomasosho's avatar
tomasosho's avatar tomasoshoCode Review
4

Optimize laravel query

Hi, I made this query to retrieve more data from a few tables .. but currently I tested with many pictures .. and it loads in 4 minutes I also tested with super many data of order 8k elements in the database and it loads super hard. Do you know a way to optimize your query? Could I use something from laravel to get rid of these joins or something? $results = app(Produc

Lokedi's avatar
Lokedi's avatar Tray2Laravel
2

Count multiple groups using eloquent in a single query

I recently had to count an arbitrary number of items (from the same table), but having the counts grouped and found a way to do this with Eloquent (and a raw statement). Below is an explanation of how it works and gets setup. I am wondering if this could be perhaps added as a way to count relations of relations directly in Eloquent. For example: Model::withCount('relation1' , '

Jacotheron's avatar
Jacotheron's avatar JacotheronEloquent
0

How to add more selects in eloquent query

Hi, I build a query and loaded relationships with it then select some columns from a model but want to add/select more columns from related modes/relationships. for example $user = User::with('comments') ->select('name', 'age') ->addSelect('comment') // i want to add columns from comments model. ->get(); is there any way to select column at this point ?

sajjadali's avatar
sajjadali's avatar MichalOrav...Eloquent
4

DB Query Builder updateOrInsert Vs Eloquent firstOrCreate ( Performance)

I'm using laravel mongodb and trying to import large data from json files. I tried to use Eloquent's firstOrCreate and Query Builder's updateOrInsert. I thought using query builder would be faster but it turned out that it is slower than Eloquent. Can you please help me insert data fast enough because I have millions of data left? The following is my code. Query Builder $ids =

w99910's avatar
w99910's avatar Tray2Laravel
3

how to build this nested query using laravel eloquent

how to build this nested query using laravel eloquent SELECT * FROM posts WHERE user_id IN (SELECT follower_id FROM followers where id =$id)OR user_id=$id;

mohamadAbdelhady's avatar
mohamadAbdelhady's avatar MichalOrav...Laravel
1

No query results for model - please help?

No query results for model [MyLocal\Modules\Media\Models\Media] {"id":185,"url":"http://mylocal.test/storage/media/YisGi9vQPvGeq5gVjtOxvY4zBJwzC4T3sITdgYsT.png","description":"Here is a Social image"}" Do I need to give permissions to my storage folder? I just ran php artisan storage:link, nothing has changed?! it said it w

boyjarv's avatar
boyjarv's avatar tykusLaravel
1

Laracasts Bug? Discussions Search Query Pagination

@JeffreyWay I was searching for a word in the discussions section of the Laracasts website and noticed that each time I clicked a pagination link to the next page of results the search query is removed from the URL. I have been adding the page query manually for now in order to retain the search filter queries in the URL. Hope this is useful.

Rory Gonzalez-Brown's avatar
Rory Gonzalez-Brown's avatar Rory Gonza...Feedback
0

Getting an attribute to be part of a query

I would like to know if it's possible to add an attribute to be part of a query. So what I mean is that in my Product model I have an attribute function that goes like this public function getProductInfoAttribute() { return sprintf("%s > %s", '['.$this->id.'] '.$this->name, $this->categories()->count()); } and what I would like is that when

Ifrit's avatar
Ifrit's avatar SnapeyLaravel
4

Complex Scope Query

Hello All i want to use user id of wherehas query in the second method. how can i achieve this? Thanks in advance Edit : memeber is a typo.. it will be agent. i have on table agent_to_manager and i want to fetch only agent roled users that is not present in agent_to_manager table.. and i have two queries one that fetch only agent role user and second checks where the fetched u

amiyo_ghosh's avatar
amiyo_ghosh's avatar amiyo_ghos...Eloquent
1

SQL to Elequent with from sub query

Hello, is there a way, to get from this SQL: SELECT *, (SELECT count(*) FROM addresssurveys WHERE savedtimestamp::date = sendout_date) AS y FROM (SELECT created_at::date AS sendout_date, created_at::date AS x FROM addresssurveys GROUP BY created_at::date) AS main an Eloquent statement? I created already the sub from query: $sub = Survey::select(DB::

maltekiefer's avatar
maltekiefer's avatar bobbybouwm...Eloquent
1

Construction and execution of both query builder and eloquent

How to understand how the construction and execution of both query builder and eloquent works? I am making a package so that elastic search behaves like a sql DB and that the eloquent and query builder utility is not lost. I tried to base myself on the jenssegers/laravel-mongodb package but the truth reached a point where I got lost because I can't find how it ends up building

JorgeAlberto's avatar
JorgeAlberto's avatar bugsyshaLaravel
1

How to query this with relationship

I have a query using join but i don't want to use join and want to get same result by using relationship $result = User::where('id', 1) ->where('deleted', false) ->leftJoin('posts', 'posts.user_id', '=', 'users.id') ->groupBy(['users.id']) ->selectRaw('users.*, count(posts.id) as post_count') -&g

david001's avatar
david001's avatar MichalOrav...Eloquent
3

Dedicated Query String Filtering: Paginate meta date

so I followed this lesson: https://laracasts.com/series/eloquent-techniques/episodes/4 for advanced query filtering. in my FundFilter class I have this class FundFilters extends QueryFilters { public function requested_by($requested_by) { return $this->builder->where('requested_by', $requested_by); } public function closed_on($order = 'asc'

malsowayegh's avatar
malsowayegh's avatar malsowayeg...Eloquent
2

n+1 query results

Hi - I'm getting n+1 warnings when I run a query using a 'with' condition. Here's the model: class Ensemblemember extends Model { use HasFactory, SoftDeletes; protected $fillable = ['ensemble_id', 'instrumentation_id', 'schoolyear_id', 'teacher_user_id', 'user_id', ]; public function person() { return $this->belongsTo(Person::class, 'user_id', 'user

Rretzko's avatar
Rretzko's avatar RretzkoLaravel
9

How to make eloquent query with 2 table and two from

Hello everyone, I have no idea, how do this query with eloquent for get object. SELECT field1, field3, total FROM (SELECT field1, field3, sum(field2) AS total FROM sales GROUP BY field1, field2 ) subtable GROUP BY field1; I mean how can I do "subtable" table after join two tables then do group by, min and group_concat and etc ... with this result. In this one

kodzero's avatar
kodzero's avatar kodzeroEloquent
0

one to may relationship query

hi, I am a bit struggling with this relation. not sure why.... Admin model. An admin has an employer public function employer() { return $this->belongsTo(Employer::class); } employer model. An employer has many admins public function admins() { return $this->hasMany(Admin::class); } The following query returns the relationship correctly b

FREDERIC LD's avatar
FREDERIC LD's avatar MichalOrav...Laravel
1