How to prevent an unfiltered query on millions of records?

I have a resource where the default filter uses a limited date range to prevent query failures on the entire database. How can I ensure users either select a narrow date range or, if no date range is specified, provide an alternative filter to avoid querying all records?

michaelchunn's avatar
michaelchunn's avatar LaryAINova
1

Doubt in Query logic in Laravel 10

Dear Friends, I have a form structure as shown below I am trying to implement a logic while adding new entry with unique short_name as public function GetPenaltyStatus(Request $request) { $start_date = $request->start_date; $end_date = $request->end_date; $short_name = $request->short_name; $flag = 0; // if flag is 0 can add new e

insight's avatar
insight's avatar SnapeyLaravel
6

Route query string values don't persist with pagination in Filament Resource

I'm using a query string to set a value I need to persist between pagination pages because I use the request() field value with the Resource's getEloquentQuery() method to alter the query results for the Resource. I used dd() for testing and can see the first visit of the Resource page contains the request() field value, but when visit subsequent pagination pages, the value is

myregistration's avatar
myregistration's avatar myregistra...Filament
0

PG SQL query return timeout when search

Hi, I have problem with my pgsql query: When i have this query: SELECT COUNT(*) AS count FROM address zk INNER JOIN dic_data t ON zk.fk_typpm = t.id INNER JOIN dic_data k ON zk.fk_kierunekpm = k.id LEFT JOIN dic_data r ON zk.fk_rolapm = r.id WHERE zk.pm = zk.id AND UPPER(t.code) = UPPER('EMAIL') AND k.code = 'CUSTOMER' AND zk.contakt BETWEEN '2024-04-01 00:00:00

trifek's avatar
trifek's avatar Tray2General
3

SQL to Query Builder Statement

Hi, I have an SQL statement that I am struggling to convert to a Query Builder statement: SELECT IF(t2.sales != 0, (t1.sales/t2.sales - 1) * 100, NULL) AS diff FROM (SELECT SUM(sales) AS sales FROM sales_user WHERE year=2023 AND month=6 AND user_id=1) AS t1 JOIN (SELECT SUM(sales) AS sales FROM sales_user WHERE year=2022 AND month=6 AND user_id=1) AS t2

StefanSchmalhaus's avatar
StefanSchmalhaus's avatar StefanSchm...Laravel
3

How to solve the issue in query ?

Dear Friends, In my query in Laravel 10 the below code $operatorRole = OperatorModel::whereNotIn('id',$newArray)->where('role', $verifierRole)->select('id','operator_code','role')->groupBy('id','operator_code','role')->get(); shows error as "message": "count(): Argument #1 ($value) must be of type Countable|array, string given", How to use $n

insight's avatar
insight's avatar insightLaravel
6

A query with pagination takes much longer than using get()

Hello, I was trying to see why a page takes over a minute to load. At first, I thought the query is too complex and unoptimized, so I directly put the raw query (given by ->toSql()) into the Microsoft SQL Server Management Studio. But to my surprise the query executed instantly (00:00:00). But in Laravel it takes over 20 seconds The difference is that in Laravel I use $query

Ligonsker's avatar
Ligonsker's avatar jlrdwLaravel
3

Trouble retrieving a relationship in a query

Hello everyone, I'm a bit stuck on an Eloquent request that's giving me a bit of trouble. The request concerns an application that manages loan applications for banking partners. A banking partner has rates, price intervals and interest rates per month for these intervals. Here are my tables: partner_rate_types : partner_id, rate_type_id interval_amounts : partner_rate_type_id,

Lopsum's avatar
Lopsum's avatar VableEloquent
2

How to forget a column from a collection returned by a query?

Hi, How can I remove specified columns from a collection after using Query Builder's get()? For example, I have a query: $collection = DB::table('table')->get(); And I get a collection of all the columns: Illuminate\Support\Collection { #items: array:100 [ 0 => { "id": "1" "first_name": "John" "la

Ligonsker's avatar
Ligonsker's avatar SnapeyCode Review
11

How do I make this complex query in laravel

i have these data Course id 1 , program id 2, code#112, start date 2023-1-11, end date 2023-2-11 Course id 2, program id 2, code#112, start date 2023-2-12, end date 2023-3-13 The result should be course id 1, program id 2, code #112 ,start_date 2023-1-11, end_date 2023-3-13, program:{..}, course:{...} the query should get 1 row of each same code data for example #112 it shoul

demonz's avatar
demonz's avatar demonzEloquent
4

Using user id inside query while fetching users

I'm trying to get All users that have more than 20 reports on them within the last 2 hours, considering the reports created after the user's last ban (if there is one) created_at value. $users = User::withWhereHas("profile", function ($query) { $query->whereNull("banned_until"); }) ->whereHas('reports', function ($query

Phil_Dr's avatar
Phil_Dr's avatar Phil_DrEloquent
2

Change State Between Page (Parent) and Datepicker (Child) Components from query string

Hi everyone I have this Datepicker component and I managed to get the state in the parent (Inertia Page) by passing a function:setParentDate import React, { useState } from 'react'; import DatePicker from 'react-datepicker'; import '../../css/react-datepicker.css'; export default function TableDatePicker({setParentDate}) { const [dateRange, setDateRange] = useState([null,

emmatraversy's avatar
emmatraversy's avatar martinbeanReact
1

Passing DB Query via Ajax to Populate Options Based on Previous Selection

I am new at this and I could be missing something very basic. I have two select boxed in my view. The first 'select' I populate via a query in my controller. I have another query in that same function that gets data for my second 'select' to be populated based on whatever is selected in the first box via Ajax. Route: Route::get( 'qryStructures_start', [\App\Http\Controllers\

stanhook's avatar
stanhook's avatar Max100JavaScript
13

Modular Laravel Query Questions

I recently watched the modular laravel series from Laracasts. It was an amazing series but it kinda missed a point for me on the topic of querying. In terms of business-logic it revolves around Actions (or Commands). But when it comes to query how do you handle it ? I'd bet you can create a similar kind of class QueryProductsForUser(int $userId) but what does that return ? Do y

ultrawelfare's avatar
ultrawelfare's avatar LaryAILaravel
1

Livewire - array to comma separated for query string

I have an array property topic on a livewire component that I want to use as query string, the query string looks like this currently example.com/?topic[0]=livewire&topic[1]=laravel, but I prefer the url to look like this example.com/?topic=livewire,laravel currently I introduce a seperate property queryStringTopic and convert the topic array to comman separated string publ

Sabonzy's avatar
Sabonzy's avatar jlrdwLivewire
3

MySQL query with belongsTo

I call DB::statement() to run a complex query involving aggregate functions, variables and subqueries in order to generate a report, basically, I don't think it can be reproduced in a readable manner with eloquent. However, on each row, there are also fields like user_id, project_id etc My question is, is there an easy way to write $record->user->name without having to w

laracoft's avatar
laracoft's avatar laracoftEloquent
3

How to query a JSON string inside of Eloquent

I have a user preference table that looks like this: public function up() { Schema::create('user_preferences', function (Blueprint $table) { $table->id(); $table->unsignedBigInteger('user_id'); $table->string('key'); $table->text('value'); $table->timestamps(); $table->foreig

phayes0289's avatar
phayes0289's avatar Tray2Laravel
2

React Redux Toolkit Query RTK Refetch

import { createApi, fetchBaseQuery } from "@reduxjs/toolkit/query/react"; import { getAccessToken } from "../../Utils/Helpers"; export const completeProfileApi = createApi({ refetchOnMountOrArgChange: true, refetchOnFocus: true, reducerPath: "completeProfile", baseQuery: fetchBaseQuery({ baseUrl: "http://192.168.3.49:80

Hassankhan's avatar
Hassankhan's avatar HassankhanReact
0

Filter with empty query value

I have a category model which can have sub-categories. id category_id name ... I have a route to search categories GET /categories/search?category_id= I want to return: i) Only categories without parents if category_id is empty but is in the query ii) All categories if the category_id is not in the query iii) Categories with category_id as a parent if category_id is in the

Udev's avatar
Udev's avatar UdevLaravel
1

Validation custom query

I am having trouble getting the following to do what I need. I have the following validation rules return [ 'facility_booked' => 'required|array', 'facility_booked.*.facility_id' => 'required|exists:facilities,id', 'facility_booked.*.start_time' => [ 'required', 'date_format:Y-m-d H:i', app()->get(FacilityStartTimeExists::class)

PierceMcGeough's avatar
PierceMcGeough's avatar PierceMcGe...Eloquent
2

Query with pagination slow performance when LESS than 10 records

I am facing slow performance on a paginated query. When it has to fetch less than 10 records, it takes one minute. When its more than 10, it takes half a minute. If it is more than a thousand it takes 5 seconds!!! Yes, the more data it has to fetch, the faster it is!!!!!! The query: $facilities = auth()->user()->facilities; $notSentStatusId = PlacementOpportunityS

Nando7's avatar
Nando7's avatar Nando7Laravel
2

How to make the dynamic query in loop to stored procedure ?

Dear Friends, I am using Laravel 10, MariaDB for my web application. My working code snippet as $phver = []; $phaseVersions = ''; $phaseMasterData = PhaseMasterModel::orderBy('created_at', 'desc')->get(); foreach ($phaseMasterData as $ph) { $phase = $ph->phase; $start_date = $ph->

insight's avatar
insight's avatar insightLaravel
11

Scout search with query

I need to user search() with custom query. $model = Products::query()->where('is_active', true)->search('Gucci')->get(); Is that ok to get data from Scout separately and search where using its IDs? public function scopeCustomSearch($query, $search): void { if(is_null($search)) { return; } $scout = $this::search($search)->

Husniddin's avatar
Husniddin's avatar LaryAIEloquent
1

Wrong model ID in belongsToMany relationship's aggregate query

I am using Filament and building a multi-tenant application. I used laravel-shift/blueprint to scaffold the database and models. I have re-re-reviewed the multi-tenant setup in the Filament docs, and am reasonably certain I did this correctly. In the Team and User models, I have the following relationships. // Team.php public function users(): BelongsToMany { return $this-

fin_keel_baldric's avatar
fin_keel_baldric's avatar fin_keel_b...Eloquent
1

query array to object

I have a query ?page=1&filter[name]=white%20t-shirt&filter[attributes][color][]=red&filter[attributes][color][]=blue&filter[attributes][size]=xl how can I convert it to a js object filter:{ name:'white t-shirt', attributes:{ color:['red','blue'], size:'xl' } after conversion and making some changes: convert back to query

Udev's avatar
Udev's avatar vincent150...JavaScript
6

Query is returning null in filament but works outside filament

please help me with this situation, if I use this relationship outside of filament it works well but inside filament, it is changing the $this->programme_id to null. public function programmeCourse(): BelongsTo { return $this->belongsTo(ProgrammeCourse::class, 'course_id', 'course_id') ->where('programme_id', $this->programme_id); } here is the resulting query insid

ritechoice23's avatar
ritechoice23's avatar horlatechFilament
10

Not working the update query in dynamic loop

Dear Friends, I am using a static loop before master table creation and it worked fine. But when master table implemented I need to run that process in dynamic loop. So I have a for loop code like below foreach ($phaseMasterData as $ph) { $phase = $ph->phase; $start_date = $ph->start_date; $end_date = $ph->e

insight's avatar
insight's avatar insightLaravel
9

Model::create() causes database query

When I execute MyModel::Create() in my code (Laravel on Postgres), Eloquent executes the following query: select a.attname as name, t.typname as type_name, format_type(a.atttypid, a.atttypmod) as type, (select tc.collcollate from pg_catalog.pg_collation tc where tc.oid = a.attcollation) as collation, not a.attnotnull as nullable, (select pg_get_expr(adbin, adrelid) from pg_att

ronmakeszens's avatar
ronmakeszens's avatar SnapeyEloquent
1

Update query for duplicates

Hi, I have small problem with my SQL Update query. I have this code: UPDATE records set kp= -kp where av=pm and secret_key = 123 and date='2014-01-21' and pm IN (SELECT pm FROM (SELECT pm, ROW_NUMBER() OVER(PARTITION BY secret_key

trifek's avatar
trifek's avatar huppGeneral
1

Paginate results from has many query

Hi everyone, I'm running into an issue on how to paginate results when query a has many relation, for instance I would like to display the results in a table using vue, paginating the has many collection. Relations are as follow: Client has many campaigns Campaign belongs to a client What I would like to achieve is paginate (5 results) the campaigns that belongs to a client for

jaumevallsvila's avatar
jaumevallsvila's avatar SnapeyLaravel
19

How can i improve query in Laravel for few milions records?

Hello everyone, I have a question regarding the Laravel query builder. I have a task where I need to retrieve only those purchasegroups from the Purchase model that have a pricelist with ordered set to true and at the same time do not have any pricelist with tax set to null. I return the data through a cron job to the log. The problem, however, is that even after 63 minutes, wh

jakubjv's avatar
jakubjv's avatar krisi_gjik...Laravel
9

Help with sql query

I have this sql query that works perfectly (returns one row): SELECT player_stats.*, opposing_team.nombre AS opposing_team_name FROM player_stats JOIN partidos ON partidos.id = player_stats.partido_id JOIN equipos AS player_team ON ( (player_team.id = partidos.equipo_local_id OR player_team.id = partidos.equipo_visitante_id) AND EXISTS ( SELECT 1 FROM eq

manuelpmgt's avatar
manuelpmgt's avatar JussiManni...Eloquent
5

Order and limit query results

Hey guy's, In the result set shown below my goal is to select distinct 'booking_event_id', using the row with the highest 'id' if there are duplicate 'event_booking_id'. In this instance, the last three rows should not be returned. {"event_booking_id":16,"id":16} {"event_booking_id":17,"id":17} {"event_booking_id":18,"id&qu

Cyburg's avatar
Cyburg's avatar CyburgEloquent
5

How to load a relation with additional conditional query correctly?

$students = Student::with([ 'user:id,name,image', 'classGroup' => function (Builder $query) use ($request) { $query ->when($request->query('grade'), function (Builder $query, int $grade) { $query->where('grade', $grade); }) ->when($request->query('major'), function (Builder $query, string $major) { $query->where('major', $major);

raflisss's avatar
raflisss's avatar amitsolank...Eloquent
6

Query Correction

I am using this Query. Safety::with( ['imageName'] )->where( ['property_id', '=', $property->property_id ], ['unit_id', '=', $property->unit_id ],['type','=', 'gas' ])->latest()->first(); I am getting error SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'where clause' . Is there any issue in the Query ?

afoysal's avatar
afoysal's avatar amitsolank...Laravel
4

Failed Query is Not throwing ModelNotFoundException

I have this function that fires an event function that retrieves a user. note: (a . there is a reason it's an event and not just calling the function directly. b. this is a Livewire controller) If the user is not found/ the dependency injection fail, I get this " No query results for model [App\Models\User] PISADM954U2023 " on the debug screen, but according to the L

PRESTIGE2930's avatar
PRESTIGE2930's avatar PRESTIGE29...Laravel
2

How can I prevent query params from accumulating each time I change the filter?

Hi everyone, I have a problem with the accumulation of query params when I choose a new filter to my select. How can I keep the current filter with each change ? Here my select : import SelectInput from '@/Components/SelectInput'; import { ApplicationData } from '@/types/generated'; import { InertiaFormProps } from '@inertiajs/react/types/useForm'; import { PropsWithChildren }

Tiskiel's avatar
Tiskiel's avatar TiskielReact
0

Nova adding duplicate aliased tables to query

I'm generating a query from a selection in Nova which is used with Laravel-Nova-Excel in an action to generate a CSV export. I'm not sure whether this problem is coming from Nova itself or that package, but Nova has had this bug in the past which is almost identical. My query method, which is based on a Scan resource: public function query() { return $this->query

Synchro's avatar
Synchro's avatar SynchroNova
2

Laravel is messing up a query. Need to run it raw

Hi all! After many hours trying to solve a problem of a query not using spatial indexes, I've bumped into two issues. First, the migration doesn't seem to create the table/index properly, and I need to run manual DB::statements to alter the table and the index in order for it to work. (edit: this might be the fault of the package I am using) Secondly, when I run a SQL statement

nunodonato's avatar
nunodonato's avatar Tray2Eloquent
7

Incorrect Spatie Query Builder field used for join in sort

I have been trying to add a simple sort to a query to sort by a date field in a related table (see this previous post for details), and have the following code: public function report(Request $request): Response { $filter = $request->query("filter") ?? ''; $between = $filter === "" ? "" : explode(',', $filter['between'])

wonder95's avatar
wonder95's avatar LaryAILaravel
1

Unable to add date sort from related fields with collection and Spatie Query Builder

I have a controller method that just generates items for a report public function report(): Response { return Inertia::render('TreasuryReport', [ 'payments' => QueryBuilder::for(Payment::class) ->with('dues.user') ->allowedFields('dues.user.first_name', 'dues.user.last_name') ->allowedFilter

wonder95's avatar
wonder95's avatar wonder95Laravel
2

I need an eloquent query to show list of blogs as per ranking

I have articles table, having column ´total_ratings´ I want to calculate ´total_ratings´ such a way that can produce me 3 articles from most likes, 2 articles from most views, 2 articles from most recent, 2 articles from less likes by using following query (as it is most optimised and by single query) ´Article::orderByDesc("total_ratings")->paginate(9)´ N.B. : If s

ridwanhoquepro's avatar
ridwanhoquepro's avatar ridwanhoqu...Eloquent
0

query on related table instead of pivot

hi guys so i have many-to-many polymorphic relation between my tables and everything is standard just like the documentation. except i added 'status' on all tables. tables are like this: posts id - integer name - string status - string videos id - integer name - string status - string tags id - integer name - string status - string taggables

komeil's avatar
komeil's avatar SnapeyEloquent
4

Accessing third level relationship field in Spatie QueryBuilder filter query

In my app I have a relationship hierarchy of User -> Dues -> Payments, where each relationship is a one to many (e.g. User hasMany Dues and Dues belongsTo User). A Dues can have multiple Payments. The dues model has a nra_dues column, and so I need to get records where nra_dues >0, and then I need to get the payment_date column from the payments table. I am using

wonder95's avatar
wonder95's avatar wonder95Laravel
2

query regarding adding multiple sessions and show them on a single page with filament

<?php namespace App\Filament\Pages; use App\Services\GameTypeService; use App\Services\SessionService; use Filament\Actions\Action; use Filament\Forms\Components\Select; use Filament\Forms\Components\TextInput; use Filament\Pages\Page; class Games extends Page { protected static ?string $navigationIcon = 'heroicon-o-document-text'; protected static string $view =

islamnouman's avatar
islamnouman's avatar islamnouma...Filament
0

Get raw results - not a collection from Query Builder

When I run a query in mysql I get a table, and what Laravel does is turn that into a collection for me. Most of the time that's amazing, great. However, I would like to build a query and get the original table back in PHP because I'm going to return the table (for reporting purposes). It seems like a waste of processing power to query -> convert to collection -> flatte

zagreus's avatar
zagreus's avatar zagreusEloquent
2

DB query report by month with the month being returned, including if the result is 0

DB::table('prints') ->selectRaw('SUM(pages* copies) AS total, DATE_FORMAT(print_date, "%c-%Y") AS month') ->whereBetween('print_date', [Carbon::now()->subMonths($qtd_months - 1)->startOfDay(), Carbon::now()->endOfDay()]) ->groupBy('month') ->get(); // Current output // ['month' => '4-2023', 'tota

fcno's avatar
fcno's avatar jlrdwLaravel
3

Query builder "where" and "when" methods not being applied together

I'm running the following query builder in Laravel 9. The "where" statement before the "when" conditional clause is only executed if the $term variable is empty. If $term is not empty, only the statements inside "when" are executed. I have tried placing the "where" after "when" but it does not work as expected either. The "

guijs's avatar
guijs's avatar guijsLaravel
2

Problem with partial match on query builder

Hi guys, i'm new to laravel. I use this query on my controller and work, this one show data of daily distribution for values that was filtered with a form, and this is modulable because when some parameters of filter are not set i don't use in my query. $query = Measure::query() ->select(DB::raw('DATEPART(hour, DATETIME_D) AS HOUR_N')) ->selectRaw('SUM(CASE WHEN STATUS_N

giupes's avatar
giupes's avatar ThundersonLaravel
1

How to optmize the query if it has multiple joins and the table is large?

$results = DB::table('tblencounter') ->join('tblpatientinfo', 'tblpatientinfo.fldpatientval', '=', 'tblencounter.fldpatientval') ->join('tblpatbilling as bd', 'bd.fldencounterval', '=', 'tblencounter.fldencounterval') ->whereRaw('cast(bd.fldtime as date) >= ?', [$fromdate]) ->whereRaw('cast(bd.fldtime as date) <= ?', [$todate]) ->whereIn('bd.fldcomp', ['com

Rock12's avatar
Rock12's avatar Rock12Laravel
6