Query with max value from multiple relationships

I know withMax() can get the value of single relationship, but i want query from multiple relationships artists table id | name | created_at | updated_at | deleted_at ------------------------------------------------ 1 | Joe | | | | 2 | Sam | | | | Artist many to many Music musics table id | name | rating | creat

newbie360's avatar
newbie360's avatar newbie360Eloquent
2

Laravel eloquant query to get records out of date range

I want to fetch the count of projects where the current date is not falling between project start/end dates. public function getTotalProjects($active = false, $running = null) { $query = Project::query(); if ($active) { $query->active(); } if ($running === true) { $query->where('start_date', '<=', Carbon::to

amitshahc's avatar
amitshahc's avatar amitshahcEloquent
9

Mysql query to Remove similar looking column rows without data loss in 2nd table

I have 2 tables : companies and company_details. where company_details is connected by companies table with companyID. I want to remove similar looking company name and keep the lowest id value incompany table and also update the companyId in the 2nd table. Is this possible to do ? Is there any MYSQL Query that can achieve this ? My Original Table : 1st Table: Companies -------

princeparaste's avatar
princeparaste's avatar princepara...Code Review
3

Save multiple records with one query

Hello friends, how can I insert many records in the database using a query and then have all the IDs?

Mamad_Ch's avatar
Mamad_Ch's avatar SinnbeckLaravel
1

How to optymalize eloquent query with large datasets

Hello Guys, I have query for calculate turnover by table documents. The problem came at a time when there was a lot of data. How can I optimize my query? The query must be performed every time to keep the data up-to-date, maybe apart from optimizing the query, I should use the cache and keep the data for 60 minutes? $turnover = Document::where('shop_id', $shop_id) -

Sinres's avatar
Sinres's avatar Tray2Eloquent
1

Adding linked table data to query builder

Hi all! I'm still getting comfortable with Laravel and am really struggling with my current project... I'm trying to implement "reposts" on my social media feed that uses Laravel for backend. I have a table for posts and a table for reposts, which contains post_id (foreign key linking to posts), user_id (who reposted), created_at (when post was reposted). I have a que

dglaser2's avatar
dglaser2's avatar MuhammadHH...Laravel
1

Help transforming MySQL query

Hi all, I'm still learning the ropes with Laravel, Eloquent and such. I can't seem to work out how to get this to run in Laravel. select asset.assetmake_id as 'Make Id', assetmake.name as 'Name', count(*) as 'Count' from asset inner join assetmake on assetmake.ID=asset.assetmake_id group by assetmake_id order by Count desc; I've tried variants of this: DB::table("asset&quo

SlimPickins's avatar
SlimPickins's avatar MichalOrav...Laravel
3

Why does Livewire making disappear relationship query?

I have a select2 its working fine. Its working fine with normal queries but when i list items with eloquent relationships it does not work normally. For example; '' Posts::withCount(['tags' => function($query) use ($category_id){ $query->where('category_id', '=', $category_id); }]->get(); '' This query gives what i wanted to. When list this query in select2 like this;

blapza's avatar
blapza's avatar blapzaLivewire
0

MariaDB query running out of memory but failing silently

I'm writing an artisan command. I've got an SQL query that runs out of memory because it's returning a large amount of data. I can fix that, the problem is Laravel isn't giving me any PHP errors. I used Tinkerwell to test the statement it and that gives me a PHP error.: Symfony\Component\ErrorHandler\Error\FatalError Allowed memory size of 536870912 bytes exhausted (tried to a

wturrell's avatar
wturrell's avatar Tray2Laravel
1

[Laravel Cashier+stripe] Query scope for users latest subscription

I am trying to create a Nova filter that will allow me to view users whose subscription is currently cancelled. I am running into issues creating the filter as I can't see a way to query against only the users latest/current subscription. The scopes Cashier provides consider all of a users past subscriptions. Is there any way to scope queries against only the latest subscriptio

MiloHP's avatar
MiloHP's avatar MiloHPLaravel
0

Query builder join table to column like a eloquent

How can we repeat eloquent query using Query builder? If we need more information from another table on author column like this { "20": { "id": 20, "title": "Quia eaque veritatis dolores eum nisi minus.", "description": "Sit molestiae fugiat iste magni ab alias sunt.", ... "author": {

Speculant's avatar
Speculant's avatar jlrdwLaravel
5

Payment::PROCESS only returns value for where payment_status = 0.How to make this query return result for where condition where payment status = 0,1

$filter->where(function ($query) { switch ($this->input) { case Payment::PROCESS: $query->where('payment_status', '=',[0,1]); break; case Payment::PENDING: $query->where('payment_status' , Payment::PENDING); break; case Payment:

nuna's avatar
nuna's avatar SnapeyLaravel
3

Eloquent query doesn't return the right answer

I am trying to bring only some of the stations, but I can't seem to be using the query right. If i put it as query()->$results it says Array to string conversion and this way it just returns all the stations public function builder() { $results = DB::select( DB::raw(" SELECT * FROM stations WHERE stations.id IN( SELECT

ichmag17's avatar
ichmag17's avatar ichmag17Eloquent
2

Cache Every Query

I saw an article a month or so ago talking about how to cache every DB query during a given request. This would remove the duplicate queries I have that have in my application that is slowing things down. I can't not find the article anymore but does anyone know how this would be done? I basically need to cache every query for that single request.

Lukebouch's avatar
Lukebouch's avatar LukebouchLaravel
15

Query builder inner join on this and that (subquery with two parameters)

Hello, I have a little problem with a query Im trying to make with the query builder : So basically I have a table with some employee's ids, a counter, and a date (which is the year) what I want is getting the last available year for each employee in one Query. I have managed to do it in raw mysql it looks like this : SELECT * from compteur_conges inner join ( SELEC

pedro4000's avatar
pedro4000's avatar pedro4000Eloquent
3

Query builder (one-to-many and many-to-many)

Hi, I have a one to one relationship (course->university) and a many to many relationships (course -> cities) I am trying to query all courses that belong to a specific university and are held in a specific city. I can get all courses that are linked to a specific (Course::where('university_id', university_id)->get()) but I cannot figure out how to also pull/filter dow

tnort's avatar
tnort's avatar tudosmEloquent
5

Query calculations with child and grandchild relationships

I have three models; Quiz, Question and Answer. All the necessary relations are set. A Quiz can have one ore more Questions, and each Question has one or more multiple-choice Answers (Also the vice versa relations are set). For each Answer of a Question different points are given. Now for a Specific Quiz I want to query the max possible points possible. So the sum of the Answer

eheins's avatar
eheins's avatar eheinsEloquent
0

Sharing eloquent query result where auth()-user() object is used with views and across the application

Hello folks, I am facing a challenge which I have usually solved by using singletons or view composers. I need to share the cart instance with the items that are in cart. Each time cart is retrieved it's from the database and based on auth()->check() I decide whether to use SessionRepository or DatabaseRepository in the app. We are also storing both session carts as well as

rudolfbruder's avatar
rudolfbruder's avatar rudolfbrud...Laravel
2

Dynamic relation query

User::with('tournament')->whereHas('tournament', function ($q) use ($pegiYear) { $q = $q->where('status', 1); // if tournament is 'type' = 'private' add sub query here ("type" => 'column name', private/public is value of column) return $q; });

VinayPrajapati's avatar
VinayPrajapati's avatar lbecketEloquent
3

Query builder not building the query correctly

What I'd like: SELECT users.id, count(users.id) as payments_n FROM users INNER JOIN payments ON payments.user_id=users.id where users.user_type = 'user_type_1' GROUP BY users.id ORDER BY payments_n DESC My attempt with Laravel: $users = DB::table('users') ->where('user_type', 'user_type_1') ->join('payments', 'payments.user_id', 'users.id') ->select("

bortz's avatar
bortz's avatar tykusLaravel
2

Is there is a better way to perform this query

select name, purchase_price from products where name='alisda' and purchase_price=1 query in MySQL is being executed too long, the table has 50m rows. What’s the best way to analyze and resolve the issue.

umairparacha's avatar
umairparacha's avatar automicaTips
9

Get pivot table data with query builder

Hello friends, is it possible to get the pivot table data with the help of Query Builder? $discusses = DB::table('discusses') ->join('discuss_tag','discusses.id','=','discuss_tag.discuss_id') ->join('tags','tags.id','=','discuss_tag.tag_id') ->where('parent_id',0) ->orderBy('discusses.id','desc') ->g

Mamad_Ch's avatar
Mamad_Ch's avatar frankieleeLaravel
3

Query Not Showing results after returning back to the view

I have a listall view which displays all the conversations from a table. I used a controller to pass in the query and display it on the page. I also created a delete controller which will then take the current one selected, match the first conversation found and delete it from the backend, api, and return back to the listall view. But the issue is when i return to the view it

sk8rboi7566's avatar
sk8rboi7566's avatar jlrdwLaravel
17

Chaining Conditional Where Clause Laravel Query Builder

Hi All, How can i chain this query together in laravel without using eloquent model, its based on input i get from user form, if the input exists, add it to where clause. I keep getting error "Syntax error, unexpecte ')" right after the 'whereIn->('c.Client_Type', ['Inbound', 'Outbound'])); $query = DB::table('Client_Inbound_Transactions as t') -&g

TuffRivers's avatar
TuffRivers's avatar TuffRiversLaravel
2

changing 'last_activity' format in query

Hi, I'm trying to get the date of 'last_activty' column of the sessions table, in the correct date format like year-month-day H:m:seconds but i don't recognize the format of the 'time' in this column, so i don't know how to do it. I'm trying to get it in a query by the way, so i will not use ' format->() '. Thanks.

The beginner's avatar
The beginner's avatar The beginn...Laravel
6

How to select all the columns of a table using query

$customer_data = DB::table('users') ->join('child', function($join) use($user_id){ $join->on('users.id','=','child.user_id') ->where('child.user_id', '=', $user_id) ->select('users.*','child.*'); })

Madan_Para's avatar
Madan_Para's avatar SinnbeckLaravel
10

get list of available categories from the query

$products = Product::when($request->search, function ($query, $search) { return $query->where(function ($query) use ($search) { $query->where('name', 'like', $searchs = "%{$search}%") ->orWhere('description', 'like', $searchs) ->orWhere('color', 'like', $searchs) -&

freemium's avatar
freemium's avatar SilenceBri...Eloquent
3

Writing eloquent Query

I have two queries I will like to re-write. The first one is a raw query I would like to re-write using eloquent DB::select('select * from asks WHERE amount IN (select MIN(amount) as lowest_ask from `asks` where `product_uuid` = ? group by `shoe_size_id`)', [$this->uuid]); This gets the lowest ask for each shoe size if you need some context. I tried many iterations but it a

seewhy's avatar
seewhy's avatar seewhyEloquent
5

Filtering a resource with query string or use an endpoint?

If I had a series of posts from my post model and I wanted to get the trending posts or perhaps only display posts from a certain category, should I: Pass a query param to do the filtering to my post controller? Have an endpoint and specific controller, e.g. /posts/trending, /posts/movies. Thanks!

panthro's avatar
panthro's avatar panthroLaravel
4

How to write the eloquent query without using DB::raw so that it doesn't have to put MySQL strict mode to false.

How to write the eloquent query to select the max value without using DB::raw and writing in pure eloquent so that it doesn't have to put MySQL strict mode to false. Means any alternative to write, DB::raw('max(price) as price') without making MySQL strict mode to false. Item::join('product', 'item.id', '=', 'product.item_id') ->select(['item_id', 'item.title',DB

Pranam's avatar
Pranam's avatar PranamEloquent
4

How to query a Model Relationship only if it exists?

I have a model Survey with a column installer_id which is related to hasOne another model Installation which is related to hasMany another model Assignment. I want to write a query to fetch all Survey where the installer_id is not null, and where IF an Assignment exists check all the assignments if any status is != 2 ie status = 0 or 1 return the survey. I tried this query bu

NellyTadi's avatar
NellyTadi's avatar NellyTadiLaravel
5

Query Builder ->first() to array instead of object

How to get the following query result into an array instead of an object ? $res = DB::connection('myconn')->table('mytable')->where('MYID', $id)->first();

devondahon's avatar
devondahon's avatar martinbeanLaravel
4

How to make Eloquent User model query API?

I'm integrating Laravel with a 3rd party app on the same domain, which provides an api with a few endpoints. If I query the api with the session cookie I can receive back the user's information. My question is how can I integrate it so that Auth::check()/Auth::user() for example queries that endpoint? The API also provides endpoints like /users, so I was curious if I could some

RoughPenguin's avatar
RoughPenguin's avatar martinbeanCode Review
3

Query builder and custom primary key

How to set my custom primary key (CUSTOMID) when using the following Query Builder request ? $user = DB::table('users')->find(3);

devondahon's avatar
devondahon's avatar tykusLaravel
1

Query builder wont accept variable

Can anyone tell me why I can not get the sub query in the function to accept the $slid parameter I pass in when calling the method? public static function scopeNav($q, $scid, $slid) { return $q->where('id', $scid) ->with('studentLessons', function ($q1) use ($slid) { // i have included here $q1->where('is_locked', false) ->whe

naykel's avatar
naykel's avatar SilenceBri...Laravel
6

Call postgres function from Laravel Query builder

I'm having trouble calling postgres function from laravel query builder, I use DB::raw(function_name(param1, param2, param3)) as calling format. It always says function does not exists, why? I use Laravel 8.

ynoth25's avatar
ynoth25's avatar tykusLaravel
7

Variable query parameter withTrashed, onlyTrashed or default

I am building a data table to show all users. It has a dropdown to select if records need to be collected with the trashed once and only the trashed ones. By default it will collect the records without the trashed once. The query looks like this: User::where('name', 'LIKE', '%'.$request->search.'%') ->withTrashed() ->orderBy($order, $direction) ->paginate(10) -&

Kimmer's avatar
Kimmer's avatar KimmerEloquent
5

How to convert query in single query with join

i have a query like below, $testsdata = Test::where('tests.course',$course_det->course_id) ->whereIn('test_id',$mappedtestids) ->whereIn('tests.test_type',['Integrated Mains','Mains']); return $testsdata = $testsdata->get()->map(function($test) use($from_date,$to_date){ $coursename = $test->c

Deekshith's avatar
Deekshith's avatar DeekshithLaravel
7

Query with a where clause and a fraction

I have this query in my controller but it does not return anything $global_size = Boutique::select('price')->where('size', 7/8)->first(); if I replace the size with an integer like so below, it works $global_size = Boutique::select('price')->where('size', 7)->first(); How can i make this query work with the size in fraction? The sizes for the boutique clothes are

Maximus1's avatar
Maximus1's avatar SnapeyLaravel
5

Add in a new table into my query

Hi, In my payments model I have created a relationship belongsTo for my donate_messages table on payment.id I would now like to bring through 'MESSAGE' into the Payment query below Thanks $query = Payment::whereIn('type', ['subscription', 'purchase'])->where('campaign_id', '=', $campaign_id)->active();

boyjarv's avatar
boyjarv's avatar SinnbeckLaravel
5

Relation in Class, need more complex query

I have a class that is a comment for a post. they have relationship and it works. Inside POST class: public function articleComments() { return $this->hasMany(ArticleComment::class,'ACM_cdiPost','id') ->orderBy('updated_at','DESC'); } So far so good. However, I really need more complex query (in sense of not to present muted or blocked users) which is no prob

beli0135's avatar
beli0135's avatar beli0135Laravel
4

laravel retrive old() wherein query values

I have 2 multiple value select lists that I pass users between them using jquery. select multiple="multiple" id="ListAllUsers" class="form-control" @foreach ($users as $user) option class="content" value="{{ $user->id }}" {{ $user->name }} /option @endforeach /select select name="user_id[]" required multiple=&quo

seularts's avatar
seularts's avatar seulartsLaravel
6

Multiple condition for query

Hi, Suppose I have a table called Book and I'd like to allows the users to filter the content based on: startWith (first letter of the book) Genre sortOrder(recently added,most popular(view count), releasedate,rating...) searchString (where title = %user_string%) What is the best way to build this query without so many if/else

jsvaryuna's avatar
jsvaryuna's avatar MichalOrav...Laravel
4

1-n query by id with eloquent

Hello everyone, I would like to know if there is a better way to do this type of query, I have achieved it through a sub query but I want to know if there is a better or more efficient way with eloquent, the case is that in a one-to-many relationship what I want the query to return is, passing a specific element through id, it returns all the elements related to it. That is, be

RodrigoG's avatar
RodrigoG's avatar RodrigoGEloquent
2

Query builder with() - morphMany()

Hello, I have a Client model which has a Contacts relationship, the contacts relationship is a morphMany relationship. When trying to return only one contact and specific columns from the contacts relationship with the with() method I get back an empty collection. If I remove the query builder and keep just the name of the relationship as the argument in the with() method I get

talel's avatar
talel's avatar talelmisha...Eloquent
3

Can you perform math calculations with builder query?

This code works: Product::where('active', 1) ->where('standard', 1) ->leftJoin('family_modifiers', 'family_modifiers.id', '=', 'products.family_id') ->select('products.*',DB::raw('(products.price*family_modifiers.modifier)+products.price as modified')) ->orderBy($request->column, $request->order)

ajsmith_codes's avatar
ajsmith_codes's avatar ajsmith_co...Laravel
2

Adding withSum and withAvg to nested query

I'm doing the following to get the number of orders attached to a purchaser: $collection = OrderEntity::with(['purchaser' => function($query){ $query->withCount('orders'); }]) what I'd like to do is also to return the sum of the orders.total and also the average of the orders.total doing this: $collection = OrderEntity::with(['purchaser' =>

automica's avatar
automica's avatar SinnbeckEloquent
9

Simple math in DB query

I am trying to query my DB and return results where the 'speed' column is 10+km/h over the 'speed_limit' column. I am using a query builder to take variables out of the request and build them similar to this: if($overSpeed != false){ $query = $query->where('speed', '>', 'speed_limit' + 10); } I've tried variations of the following: $query = $query->where('speed', '>

SlimPickins's avatar
SlimPickins's avatar SlimPickin...Code Review
4

Complex Laravel Query

I need to query some info in Laravel as per the description below; Model(Standing) -league_id -team_id -overall_league_position Model(Fixture) -league_id -fixture_hometeam_id (similar to team_id above) -fixture_awayteam_id (similar to team_id above) -fixture_hometeam_ft_score -fixture_awayteam_ft_score -fixture_date -fixture_status Variables -$a -$b -$c -$d -$e -$f -$g Query de

timkariuki's avatar
timkariuki's avatar esoroneEloquent
1

Livewire query string

Is it impossible to have query strings like this with livewire? https://spatie.be/docs/laravel-query-builder/v3/features/filtering#exact-filters GET /users?filter[name]=John%20Doe&filter[city]=NY I tried this way, but it don't work (https://laravel-livewire.com/docs/2.x/query-string) Looks like it doesn't support array values Also, I tried change url via js const url = new

keizah7's avatar
keizah7's avatar keizah7Livewire
2