Eloquent Query with Relationship is not working.

Earlier today, the AI response gave me what seemed to be a logical response to a big problem I was having. But I tried to implement the solution is recommended but am getting an error. It is almost as if it does not see the relationship. So, I have the following query on a Training controller page that is generating an error. $roster = User::with('currentAssignment')

phayes0289's avatar
phayes0289's avatar SnapeyLaravel
3

Laravel API slow query

Guys, how are you? I have a problem with an API I made for a client. This API consists of querying an endpoint every 2s to see if there are notes. Whenever a query is performed, it needs to be completed for another to be started, that is, they do not accumulate. route: Route::get('notes/{tenant}', [InvoiceController::class, 'index']); controller: public function index(string $

jksantos's avatar
jksantos's avatar MohamedTam...Laravel
7

Query filter by date [laravel 9]

Hi. I open this thread because I have a problem running these queries: $startDate = Carbon::createFromFormat('d/m/Y', '16/06/2023')->startOfDay(); $endDate = Carbon::createFromFormat('d/m/Y', '20/06/2023')->startOfDay(); $query = Contacts::whereDate('created_at', '>=', $startDate)->get(); OR $query = Contacts::whereBetween('created_at', [$startDate, $endDate])->

surfweb's avatar
surfweb's avatar surfwebLaravel
17

Error accessing field in multi-level nested relationship in Query Builder

I am attempting to do some filtering using a multi-level nested relationship (User-> Dues ->Payment is the model hierarchy), like so. return Inertia::render('TreasuryReport', [ 'payments' => QueryBuilder::for(Payment::class) ->with('dues.user') ->allowedFields('dues.user.first_name', 'dues.user.last_name')

wonder95's avatar
wonder95's avatar LaryAIEloquent
1

Query with million records very slow

I have a database of images stored in AWS. At the moment it has 12M records. I need to check if each image really exists in AWS, and if so, copy it to another server. So far this is ok. The problem is that for about 5M images one of the fields (u_id) is 0, these should be filtered out, they should not be included in the query. This is the basic query: do { $pixes = P

delocal's avatar
delocal's avatar delocalEloquent
7

Improve a slow query

This is my API query. It is super duper slow. (18380 ms and 10MB memory according to telescope. By the way, anyone has a clue how to make the debugbar work on API's as telescope is too slow for me) I know there are probably many things that can be improved, but I would like to hear from more experienced devs about what can be done. Appreciate every response, especially the crit

Dave Wize's avatar
Dave Wize's avatar Dave WizeEloquent
6

Flatten Eloquent Query results for CSV export

I want to flatten an eloquent query result which includes a number of many to many relationships using pivot tables. So all the returned data is in a single dimension, with the for export to CSV (using maatwebsite/excel). Here's what I have in the Export class: <?php namespace App\Exports; use App\Models\Chit; use Maatwebsite\Excel\Concerns\FromCollection; use Maatwebsit

jhob101's avatar
jhob101's avatar vincent150...Laravel
1

Need help with URL Query Parameter Issues in Vue 3, and Inertia

Hey, I'm trying to implement a little filter feature in my Laravel, Vue 3, and Inertia v1.0 app. I'm utilizing Spatie's Laravel Query Builder package. I already implemented the backend logic, so to speak, which was much easier to implement. However, I'm facing some issues or maybe I'm doing the right thing in the wrong way... Here's what the filter component's logic part looks

alnahian2003's avatar
alnahian2003's avatar alnahian20...Inertia
0

Laravel Improve Sql Query WithCount using CASE

Hi i want to improve my Query for dashboard stats i use withCount but its slow so i try to to use CASE Sql My Code : public function getWilayasApi() { $ids = Stats::where('order_stats',5)->pluck('id_stats')->toArray(); $dataWilayas = Wilaya::select('wilaya.mat_wilaya', 'wilaya.nom_wilaya') ->with([ 'statsPerMonth' => function ($query) { $query->sel

souheibmadi's avatar
souheibmadi's avatar souheibmad...Laravel
6

Parent query in relation

Eager loading relationships with "with" has been really good for performance, so far. I have been making my own custom relations by extending base class, so i can eager load some more complexly tied tables, and I've been following similar philosophy in finding related row set: by using combination of whereIn for addEagerConstraints implementation. However, in times of

peca_divljak's avatar
peca_divljak's avatar jlrdwEloquent
3

Help with a query

Hello, i have a query looking like this: DB::table(DB::raw('(SELECT *, MATCH(name) AGAINST('DDR5' IN BOOLEAN MODE) AS relevance FROM products) AS innerTable')) ->where('innerTable.relevance', '>', 0) ->get(); But i need to rewrite it so it starts from the products-model instead of a raw query. Any idea on how i can do this?

mgyback's avatar
mgyback's avatar LaryAIEloquent
1

How can this query be optimized, if possible?

Hello everyone Please review my query and provide recommendations to optimize it further. Currently, the query takes approximately 7 seconds to execute against 1.4 million records. The 7 second execution time for the query is not a big problem. The real issue arises when users apply different filters on the product page to filter product information. The current issue is that d

Yasir Bilal's avatar
Yasir Bilal's avatar Yasir Bila...Laravel
2

shell_exec, exec discard all query parameters apart from the first

I'm running this command from php 7.4 (I know it's EOL): shell_exec("curl https://$domain/$url.php?importid=$importid&org=$org&t=".urlencode($t)." >> /var/log/bgrd_call.log 2>&1 "); The nginx/access.log file shows it correctly substitutes all variables with runtime values but try as I may, it only passes the first query parameter and tr

aylara's avatar
aylara's avatar aylaraGeneral
2

SQL query to update a column to be foreignId and onDelete Cascade

I'm working on a live laravel website on cPanel and I'd like to update the user_id column on the properties table to be foreignId and onDelete Cascade. I have tried the following: ALTER TABLE properties ADD CONSTRAINT fk_properties_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; But it's not working(deleting a user doesn't delete its associated property) and

Alphy Gacheru's avatar
Alphy Gacheru's avatar Alphy Gach...Laravel
5

Eloqent Cross Table query

Hi folks :-) I wish to query across tables with an OR relationship but can only work out how to do an AND. In my code below I wish to find data with a blank email (user table) OR a short phone number (member table). (user is related to member 1 to 1) Thank you :) $users = $users->with('member'); $users = $users->where( function($query) {

Lozza's avatar
Lozza's avatar LozzaEloquent
2

API with JSON response: how to paginate when using Model query builder

I am writing an API and have a bit of a problem. I want to filter the model based on provided data, but i'm a bit lost on how to utilize the ResourceCollection properly, since I also want to paginate (dynamic options, not fixed). This is the URL that's being called: v1/configuration/companies?sort=&direction=asc&page=1&length=1&filter[full_name]=test So as you c

depalmo's avatar
depalmo's avatar LaryAILaravel
1

Multi sum with condition in single query

Hello, I'm trying to get sum of amount with different different types in where condition as show below: foreach ($yearMonths as $yearMonth) { foreach ($yearMonth as $year => $month) { $result = DailyTransaction::select([ 'transMonth' => function ($query) { $query->select(DB::raw("MONTH(transac

V9ą¤¦'s avatar
V9ą¤¦'s avatar V9ą¤¦Laravel
2

How to write a eloquent sub query with from clause.

I have the below query which I use to get max 3 months sales date where quantity is greater than 0. The query itself is fine and I get the correct data but how can I write this plain query into Laravel with eloquent relation? select * from (select productCode, quantity, salesDate from sales where productCode = "1001000029" and salesType=0 AND quantity>0 order by sa

mzbk's avatar
mzbk's avatar LaryAICode Review
1

Why making negative test I got No query results for model error

I send negative id into items.update request: public function testNegativeUpdatedAsNotFound() { ... $itemModel = Item ::factory() ->languagePrefix($language->prefix)->create([ 'language_id' => $language->id, ]); $response = $this ->put(route('items.update', -$itemModel->id), [ // NEGATIVE VALUE

PetroGromovo's avatar
PetroGromovo's avatar m7vm7vLaravel
4

Creating an eloquent query using polymorphic tables

Hi, I've the next structure in DB: plants (id, name, etc...) plants_sunlights(id, plant_id, sunlight_id, ....) sunlights(id, name,...) According that I have the next relationships between 3 tables to perform a n..m relationship: plants_sunlights.plant_id = plants.id and plants_sunlights.sunlight_id=sunlights.id I have too 3 models: Plant, PlantSunlight and Sunlight, and I hav

mp3man's avatar
mp3man's avatar LaryAIEloquent
1

Slow Query from a table with 7 mil+ records

Hi All, I have inherited a system that has a table for tracking page views. The table currently has around 7 million records in it. I am using livewire and have written a query to display page views in a datatable. The problem that I am having is that the query is taking like 20+ seconds, i assume this due to the number of records in the table. $query = TrackingView::query()

jlcain3's avatar
jlcain3's avatar LaryAIEloquent
1

sql query to remove duplicates doesn't work

I have the following mysql query: select id, titulo_filme,nota_imdb,tempo_duracao, count(*) from imdb group by titulo_filme,nota_imdb,tempo_duracao having count(*) > 1 It works as expected and returns all duplicates. But when I remove the query to delete it is treating the string field as if it were an integer, and this generates a syntax error. See the generated query to d

mvnobrega's avatar
mvnobrega's avatar SnapeyGeneral
7

putJson() results in "No query results for model"

Modifying a tutorial from Laravel Daily, I have two models, User and Vehicle, that share a many-to-many relationship through the user_vehicle table. This is done because it's possible that two (or more) users can be listed as drivers of the same vehicle. And, of course, they could be drivers of more than one vehicle. I have a partial test built that creates one of each, attache

Sturm's avatar
Sturm's avatar SturmTesting
12

Report Query for all of the Controllers

I need to run a global query and based on their database role, they will be able to see a list of reports that they have access to. So for each controller, what is the best way, to run a query from a global query and return a list of records (which are report listing) so they can see it in their blade file as a view. For example: for FinancialController, I would need to run a

johnw65's avatar
johnw65's avatar johnw65Laravel
5

How to combine multiple sum() in 1 query?

Hi, While building a simple invoicing system, I have some total stats on top of my index view. Stuff like 'total open', 'total paid', 'total late', etc. For this I use the eloquent sum() method. But I'm doing it multiple times, for each value. I'm looking to do it in 1 request. Any advice? This is my current render function in livewire component: public function render() {

tvbz's avatar
tvbz's avatar tvbzEloquent
2

How can I create user subscription revenue analytics with laravel query builder

I have a subscriptions table that looks like this Subscription table | id | user_id | service_id |amoun_paid|created_at| | -- | ------- | ---------- |----------|----------| | 1 | 2 | 1 |8.99 |2022-09-09| | 2 | 200 | 3 |89.99 |2022-09-20| | 3 | 212 | 4 |90.99 |2022-09-19| | 4 | 209 | 5 |10.99 |2022-09-05|

caleb65's avatar
caleb65's avatar caleb65Laravel
0

Multiple query on same model

I have a post model with different categories and number of views. I want to show only popular posts by views in one section whiles featured post in another section and one post category in another section on the same page, and this is how i approach it. $pressrelease = Post::query()->pressReleases()->get(); $popular = Post::query()->popular()->get(); $f

spiritkiddie's avatar
spiritkiddie's avatar SnapeyLaravel
4

Query has() with can()

Hello, I have this code. ->when($filters['with_at_least_two_events'], function ($query) use ($filters) { if ($filters['with_at_least_two_events']) { $query->has('events', '>=', 2); } }) Is it possible to apply a policy to take into account only the events that the connected user can see ? I need to have something like this. $query->has('events', '&g

vincent15000's avatar
vincent15000's avatar vincent150...Eloquent
8

Sql query not work

I am trying to filter data with search terms. But its not populate data. Any one can help me with this? Here is my code: $query = Lesson::select('lesson.*') ->join('lesson_language', 'lesson_language.lesson_id', 'lesson.id') ->leftJoin('content', 'content.lesson_id', 'lesson.id') ->leftJoin('lesson_role', 'lesson_role.lesson_id', 'lesson.id')

sanjayacloud's avatar
sanjayacloud's avatar sanjayaclo...Laravel
0

How to fill the search input with the suggested query when clicked in Algolia autocomplete form.

I have the below code and when a user types something they get suggestions. How do I feel the input with the suggestion clicked by a user? <div class="app-container"> <h1>Vue Application</h1> <div id="autocomplete" /> </div> </template> <script lang="jsx"> import { h, Fragment, render, onMounte

Alphy Gacheru's avatar
Alphy Gacheru's avatar Alphy Gach...Vue
0

Eloquent/SQL Recursive polymorphic (dynamic table) query

Hi, I want to make a recursive query based on polymorphic relation. Do you know how to achieve this ?

louisph's avatar
louisph's avatar louisphLaravel
5

Query cache does not store items

I'm getting confused, I'm trying to save a query in the caches to lighten the load: public function getListYearGame($year,$month) { if ($month < "13") { $expire = Carbon::now()->addMinutes(10); $name = 'listGame'.$month.''.$year; $el_giochi = Cache::remember($name, $expire, function() { return InfoGame::with( 'games:id,nome,copertina,cover,slug',

Xanger's avatar
Xanger's avatar LaryAILaravel
1

Query slow loading

I have a query that selects similar articles based on the hashtag of the article. $similar_art = App\Models\Article::where('is_published', '0') ->where('id', '!=', $article->id) ->where('tag', 'LIKE', '%'.$value.'%') ->inRandomOrder() ->limit(5) ->get(); However, this query slows down the page loading so much, how can I improve it?

Xanger's avatar
Xanger's avatar sureshrama...Code Review
5

Convert SQL query to eloquent query with relationship in Laravel

I want to convert this SQL query to an eloquent query : ā€‹SELECT round(AVG(rating.reviews),2) as avg_rating, COUNT(excursions.id) as review_total, excursions.name FROM reviews LEFT JOIN excursions ON reviews.excursion_id = excursions.id GROUP BY excursion_id ā€‹

faisals10's avatar
faisals10's avatar jlrdwLaravel
1

Eloquent Query Problem

Hello! Im having trouble with this query. It should bring all records that belongs to one user. The problem is that "facturas" can have one user and depends on "clientes" which also has the user. The thing is than Facturas table can be fetched by its field vendedor_id but is nullable and through its relation with Clientes table which also has its field vende

mfdzmirabal's avatar
mfdzmirabal's avatar LaryAILaravel
1

Slow query in terminable middlware

I'm trying to troubleshoot a bug in my Laravel 6.x app. I have a terminable middleware that runs the following query: $deviceHistory = DeviceHistory::where('client_id', $device->client_id) ->where('device_id', $device->id) ->whereDate('created_at', '>=', Carbon::now()->subSeconds(10)) ->withTrashed() ->latest() ->first(); Which

robinsonryan's avatar
robinsonryan's avatar robinsonry...Eloquent
5

Split out custom filter from other filters in Laravel Query Builder SQL query

I'm using Spatie's Laravel Query Builder to handle filtering for a large table, and have created a custom filter: use Illuminate\Database\Eloquent\Builder; use Spatie\QueryBuilder\Filters\Filter; class NameMemberNumberFilter implements Filter { /** * @inheritDoc */ public function __invoke(Builder $query, $value, string $property) { $query->whe

wonder95's avatar
wonder95's avatar LaryAILaravel
1

Add where Subquery using the model query builder

I want to do this subquery inside the where, but using the model query builder and not the default eloquent query builder. I have this Query Builder for Message: <?php declare(strict_types=1); namespace App\Domains\Message\Model\Builder; use App\Domains\Message\Model\Message as Model; use Illuminate\Database\Eloquent\Builder; class Message extends BuilderAbstract { pu

eusonlito's avatar
eusonlito's avatar eusonlitoEloquent
6

How to restrict with records returned in query

I have 4 models in my app, category, product, sku, and client. Category has many product and products have many skus. Clients have many skus and skus have many clients. class Category extends Model { public function products(): HasMany { return $this->hasMany(Product::class); } public function skus() { return $this->hasManyThrough(Sku:

peterdickins's avatar
peterdickins's avatar LaryAIEloquent
1

Laravel query not working properly

I have an error in this query please help me resolve this error $topMembers = DB::table('members')->join('games', function ($join) { $join->on('members.id', '=', 'games.player1_id') ->orWhere('members.id', '=', 'games.player2_id'); }) ->select('members.id', 'members.name', DB::raw('SUM(CASE WHEN games.player1_id = memb

Moeez448's avatar
Moeez448's avatar LaryAILaravel
1

Refactoring / Optimising Query

Hi, I have the following query & I have just noticed a problem it is selecting every single user from the database, where I want it to only select the owners (users) from the database who have the last reply (if there is a reply) from the users database. I'm not certain why it's pulling every user from the database in the section where it says ->with('owner:id,username,e

CamKem's avatar
CamKem's avatar vincent150...Eloquent
5

Laravel query with two different model relationships

Guys, I need to put together a cute query. Where I have it return data from a relationship and data from another relationship. There are 3 tables. Block, row and column. A block has multiple rows and multiple rows have multiple columns. So I need a query to just return these relationships. I know I can do it with innerjoin, but it doesn't bring it in an organized way, I'll have

GobssRuiz's avatar
GobssRuiz's avatar MohamedTam...Laravel
2

Redirects with Query String Laravel

Is it really necessary to write logic for handling query string on redirects(), or does Laravel have a built in method for handling the query strings in redirects? From memory, in my last project I didn't need to worry about this, it just worked fine, so I am not sure whats going on... This is what I am working with to make sure that when someone submits a message, the store me

CamKem's avatar
CamKem's avatar CamKemLaravel
1

Query for calendar events for a specific month

I'm trying to get the correct calendar events with the month and year values. This is what I tried but I know it's wrong as it brings the events for the month they belong but also a bunch of adjacent ones too. return Event::whereHas('users', function ($query) use ($request) { $query->where('user_id', $request->user()->id); }) ->whereM

eggplantSword's avatar
eggplantSword's avatar BeptumEloquent
2

How to use orWhere in eloquent query

Hello i have this query but makes error: $contactBorrowedThisCar = CarBorrow::query() ->where('borrowed_from_user_id', $userId) ->orWhere('car_owners.user_id as owner_id' , $userId) ->where('borrowed_to_user_id', $contactId) ->where('car_id', $carId) ->join('car_owners','user_id','car_owners.user_id')

devkon98's avatar
devkon98's avatar LaryAILaravel
1

How to use whereIn with Join eloquent query

Hello i have this query but its not working: $hasBorrowedCars = CarBorrow::query() ->where('borrowed_from_user_id', $userId) ->whereIn('car_owners.user_id as owner_id') ->join('car_owners','user_id','car_owners.user_id') ->exists();

devkon98's avatar
devkon98's avatar LaryAILaravel
1

Eloquent orWhere Join query makes error

I have this query: $hasBorrowedCars = CarBorrow::query() ->where('borrowed_from_user_id', $userId) ->orWhere(function ($query) use ($userId) { $query->join('car_owners', 'user_id', '=', 'car_owners.user_id') ->where('car_owners.user_id'', $userId); }) ->exists(); but makes me thi

devkon98's avatar
devkon98's avatar devkon98Laravel
0

Trying to user OrWhere with Join in eloquent query

Hello i have this query that i need to use orWhere but makes error, this is my query: $contactBorrowedThisCar = CarBorrow::query() ->where('borrowed_from_user_id', $userId) ->orWhere('car_owners.user_id as owner_id' , $userId) ->where('borrowed_to_user_id', $contactId) ->where('car_id', $carId) ->join('c

devkon98's avatar
devkon98's avatar LaryAILaravel
1

what to test when doing an eloquent select query?

If I query $result = ModelName::where(whatever)->get() I expect a collection of results. Of course, there are a whole bunch of things that can go wrong. no records returned no table associated with model heck, I can't think of all of them So what should I test for: a thrown error? null? an empty collection? And what means what? This isn't all documented in one place. O

edgreenberg's avatar
edgreenberg's avatar jlrdwEloquent
5

How to store the results of a date range in cache so that when I leave a page to another (pagination) Laravel yajra datatable sever side no longer launches the query in the database to display the results

Hello, I want that when I select a date range that laravel yajra datatable store the data in cache so that when I do the paging that yajra no longer run the query in the database. COntroller: public function paydrc_daterange(Request $request){ $transactions = []; if(request()->ajax()) { $transactions = []; if(!empty($request->start

henock_barakael's avatar
henock_barakael's avatar LaryAILaravel
1