DB query improvement for Notifications grouping

Hey All, How can i optimize my below DB query and i also want paginated data. $notifications = \App\Models\Notification::query() ->select( 'notifications.*', \DB::raw('COUNT(*) as notifications_count'), \DB::raw('MAX(created_at) as max_created_at'), \DB::raw('( SELECT GROUP_CONCAT( CO

amitsolanki24_'s avatar
amitsolanki24_'s avatar amitsolank...Laravel
4

Query is not working properly ?

protected function updateFulfillment(array $fulfillments): void { foreach ($fulfillments as $fulfillment) { $fulfillmentRecord = Fulfillment::where('line_item_code', $fulfillment['line_item_code']) ->where('fulfillment_code', $fulfillment['fulfillment_code'])->first(); if ($fulfillmentRecord) { $fulfillm

salmankhan2482's avatar
salmankhan2482's avatar jlrdwEloquent
6

Laravel Query optimization, and efficient Way to Calculate Employee KPIs

Hi everyone, I'm working on a system to calculate employee KPIs based on the actions they take during negotiations, this functionality contains returning of KPI and percentage of employee's kpi, according its created action, i am creating KPI here choose action option and quantity of each action option and add, after i should calculate of each kpi item according employees creat

GeorgeKala's avatar
GeorgeKala's avatar Tray2Laravel
1

How to determine whether to use query parameters or body in GET requests when integrating with different APIs?

I am working with two different APIs, both using GET requests but handling request data differently: API 1 requires the data to be sent in the request body, even though the HTTP method is GET. Example: $config = [ 'method' => 'GET', 'url' => 'http://example.com/api/endpoint', 'headers' => [ 'Content-Type' => 'application/json', ], 'body

rohansinghrawat's avatar
rohansinghrawat's avatar rohansingh...Requests
5

Problem query json fields using eloquent ORM

Here is the situation My model's got a json feild (getting stored as longtext in Mariadb 10.6.20-MariaDB-cll-lve) and I need to query and find in this feild. Using elequoent ORM this is my query Model::whereRaw('JSON_CONTAINS(search_keywords, ?)', [45]); and calling toSql() on it returns following select * from `blogs` where JSON_CONTAINS(`search_keywords`, ?) when ->get()

BladeOfGaladrial's avatar
BladeOfGaladrial's avatar BladeOfGal...Eloquent
2

Eloquent query question using same collection

Hi. Im trying to use the same collection to reduce the speed and optimise the site a bit and hitting an issue. Im not sure if theres a way to do this or not, I know the issue I'm facing with it but trying to figure it out slowly. So I want to grab the Ledgers associated with that user, then I need to split out the transaction types into their own variable for the frontend. I th

skoobi's avatar
skoobi's avatar skoobiEloquent
3

Help needed to optimize an SQL Query

I have written this query but it is so slow. Please help me optimize it if (request()->filled('starting_date') && request()->filled('ending_date')) { $startingDate = Carbon::parse(request()->input('starting_date'))->startOfDay(); $endingDate = Carbon::parse(request()->input('ending_date'))->endOfDay(); $dateQuery = " A

mfiazahmad's avatar
mfiazahmad's avatar Tray2Code Review
2

Query with relationship very slow

Everything was working normally until the 'Estabelecimentos' table in the database was corrupted, and I deleted it and created it again. After that, this simple query started to run very slowly: $estabelecimento = Estabelecimento::with(['socios'])->where('cnpj_inteiro', $slug_cnpj) ->first(); the cnpj_inteiro column as well as the id in the 'socios' table are properly in

mvnobrega's avatar
mvnobrega's avatar Daniel Ike...Eloquent
10

How to set up DateTime on specific column while getting result from query builder?

I can not use $cast cause I am using query builder. But I dont like to get date time columns as string. Is it possible to get DateTime object instead? $closed_dates_tmp = DB::table('devices_closed_date') ->select('*') ->where(function (Builder $q) use ($from, $to) { $q->where(function ($q) use ($from, $to) {

Čamo's avatar
Čamo's avatar ČamoGeneral
2

Centralizing Query Logic in Laravel: Best Practices for Reusable Functions

I’m developing a website where I need to fetch a list of users on some pages, but I only need limited data about the users, not all their details. Currently, I can handle this using models and write queries in each controller. However, I’m searching for a simpler approach where I can centralize the query logic in a single place (a "middle layer"). For example, if I ne

bishtyogeshsingh's avatar
bishtyogeshsingh's avatar bishtyoges...Laravel
3

Where to store init sql query with databsase mode settings

Hi there, I need to run this sql DB::statement("SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'"); // This is copy from MySqlConnector.php It is modification of MySqlConnector.php::configureConnection() without ONLY_FULL_GROUP_BY. I want to use strict mode but without ONLY_FULL_GROUP_BY. ChatG

Čamo's avatar
Čamo's avatar Tray2General
10

SQL query very fast in phpMyAdmin but slow in Laravel application

here is my sql query and it returns 1000 rows. SELECT COUNT(*) AS `Rows`, `OrderID` FROM `order_items` WHERE `OrderID` IN(1,2,3,...,1000) AND `Status` != 0 GROUP BY `OrderID` here OrderID, Status columns are indexed, when run this query in phpmyadmin it takes 0.0082 seconds, but same query takes 0.5 upto 0.9 sec in laravel eloquent OrderItems::selectRaw('count(*) as `Rows`, `O

smartadmin's avatar
smartadmin's avatar smartadminEloquent
17

Filter query result based in get parameters.

Hey guys, I am working on a filtering functionality, the filter is based on the query parameters coming from the URI. I have a load of parameters (17 to be precise), some of them match the exact column names on the Model I want to filter on. However I have data that I have to access through intermediate tables and data that don't match the exact column names as well as date ran

Trukken's avatar
Trukken's avatar N3rdwar3Eloquent
6

Uniq top list ticket per user - query

In my Laravel project, I have a Ticket model and a Player model. The Ticket model has the columns: id, external_player_id, hit_quota, and created_at. It has a belongsTo relationship with the Player model. The Player model has the columns: id, external_id, name, and status. It has a hasMany relationship with the Ticket model. I have a TicketController with a method currentTopTic

niiwill's avatar
niiwill's avatar tisuchiLaravel
1

Livewire search not rendering properly first query

Hi, I am creating a basic search for products in the database using Livewire, with a custom page in FilamentPHP, which is essentially a Livewire page component. The problem: When I enter the first query, it displays the results, but it doesn’t hide the previous ones. Also, when I hover over each previous search result component, it disappears. Note: This only happens for the fi

Manouj's avatar
Manouj's avatar ManoujLivewire
3

How to remove the Pivot of query result?

i have this query $classroom = Classroom::with('students:id,name')->find($classroom); its give-me the result Array (     [0] => Array         (             [id] => 1             [name] => Adriel dos Santos Azevedo             [pivot] => Array                 (                     [classroom_id] => 1                     [student_id] => 1                 )  

carcleo's avatar
carcleo's avatar DevioEloquent
3

Resource Controller PATCH - no query result for model

Using the following route definition Route::group(['middleware' => ['auth:sanctum', 'clean-input']], function () { Route::resource('custom-field-definition', 'CustomFieldDefinitionController')->except([ 'edit', 'create' ]) ->middleware('bind-workspace-model'); }); for a CustomFieldDefinitionModel with all of this route wrapped in Route:

wonder95's avatar
wonder95's avatar vincent150...Laravel
6

calculate average value inside SQL query

I'm struggeling with the calculation of an average value inside an SQL / eloquent query. I would like to be able to sort the users by feeback rating, but as you can see rating is calculated from already loaded models by using an accessor, is there a way I can sort directly by SQL (eloquent) query? Accessor: public function getAverageUserRatingInPercentageAttribute() { retur

t0berius's avatar
t0berius's avatar t0beriusEloquent
6

MySQL Query within a Query

Hi, I just need a little help, I am not the best at MySQL queries. I have this query that returns and ID based on info I get from the URL: SELECT wp_postmeta.post_id FROM wp_postmeta WHERE wp_postmeta.meta_value = '5mt3807' The ID that returns I need to use in this query: SELECT wp_posts.ID, wp_posts.post_content, wp_posts.post_title, wp_posts.post_excerpt, wp_posts.post_nam

stanhook's avatar
stanhook's avatar stanhookGeneral
3

Export query result as SQL Insert Statement

I would like to export specific rows from a MySQL table to a SQL file in the format of Insert Statements (similar to how PhpMyAdmin or other SQL clients do it). The data to export will be the result of a simple SQL query on a single table. I'm looking for a simple way to accomplish this using PHP or Laravel, as an alternative to doing it directly via CLI with the mysql client o

catlander's avatar
catlander's avatar LaryAILaravel
1

How can I adapt a Laravel query?

There are 3 tables with a relationship: Test1, Test2, Test3. They contain primitive data, the "name" and "desc" fields. Connected one to many: Test1(id)->Test2(test2_id), Test2(id)->Test3(test3_id). I unload data from them using the hasMany connection like this: public function method1() { return $this->hasMany(Test2::class, 'test2_id', 'id')->wi

wim91's avatar
wim91's avatar wim91Laravel
2

First value is repeated for each query of nested array validation !

The rule is 'val.*.bond_row_id' => [ 'required', Rule::exists('general-data.rased_eftetahies', 'id')->where(function (Builder $query) { return $query->where('branch_code_id', $this->def_branch) ->where('product_code', $this->input('val.*.product_code')) ->whe

mohammedawni's avatar
mohammedawni's avatar mohammedaw...Requests
0

Looking for suggestions to reduce query count for index page

In this request I AM looking for alternative suggestions to what I am prompting in regard to the way I retrieve data. I am trying to achieve an index page similar to SAMPLE where I have pinned post at the top, then for each of the parent categories (there will be multiple child categories displayed for each parent) I want to display either the most popular or the newest. So I

jcc5018's avatar
jcc5018's avatar LaryAILaravel
1

Querying PostgreSQL Range Date Column Type with LOWER function Always Return Empty Data, but Query Works in PostgreSQL

Hi everyone, I'm facing an issue where a query in Laravel, using both Eloquent and Query Builder, always returns empty results, even though the same query works perfectly fine when run directly in PostgreSQL (using DataGrip). Here’s the code I’m using in Laravel: Using Eloquent: $pertemuans = Pertemuan::query() ->whereRaw('LOWER(waktu) BETWEEN ? AND ?', [ '2024-0

triadi's avatar
triadi's avatar triadiEloquent
2

query after "limit()"?

I want to do something like read the last item and put the where condition on it, inside the relationship. Something like this: if (request()->flm_long_ticket) { $sot->whereHas('status_work', function($query) { $query ->where('status_work_code', 'OPN') ->orderBy('created_at', 'desc') ->limit(1) // here I tried t

ioiofadhil's avatar
ioiofadhil's avatar Tray2Laravel
3

Access column name into another relational query function

I have a scenario where I need to check the column from two different table (code snippets below) into the additional query. But payment_methods.last_activated is not accessible into the closure. My requirements are to fetch all payment methods as well as all the payment which has been made with that method after it has been activated. How can I do it? Below code is not working

pranaycb's avatar
pranaycb's avatar tykusLaravel
3

How to use PHP preg_replace on a formatted number based on search query while ignoring comma and decimal?

I want to highlight my search query on a formatted number. For example: $search_query = '1234'; // or $search_query = '7800'; $formatted_numeber = '12,345,678.00'; Currently my code is: preg_replace('/(' . $search_query . ')/i', "<span style='background: yellow'>$1</span>", $formatted_numeber); But this code fails to highlight search clause like '1234' ,

FCNahian's avatar
FCNahian's avatar FCNahianPHP
1

Customize the search query in the TextColumn searchable

I have created a page and on it I have the following code that shows a table with the name of the enrolled student according to the school cycle determined by "request('schoolCycle')". public static function table(Table $table): Table { return $table ->query( Enrollment::query() ->with(['student','schoo

uicabpatweyler's avatar
uicabpatweyler's avatar LaryAIFilament
1

Add custom property and resolver in Laravel Lighthouse query

I have a Customer model / table that I want to query using graphQl (Lighthouse). It should be 99% mapped into model but I want to add additional property into that query. My Customer model have: id, name, identification_number... but as a part of query response I want to add validate property which should be resolved by api call using identification_number from model as argum

knubbe's avatar
knubbe's avatar LaryAILaravel
1

Query through two related models

I have 3 tables with following structure: servers: // Server.php id, name... ports: // Port.php id, server_id, network_id... networks: // Network.php id, name... Is it possible to query all related networks for current server instance?

knubbe's avatar
knubbe's avatar ChingyLaravel
2

How to add number of days to date column in query

I have invoices table where I have date and pay_deadline. Pay deadline represent number of days from date of invoice that you have a time for pay. For example date=09/01/2024 and pay_deadline=7 that means that you have 7 days from 09/01/2024 to pay. My question is: How can I query for example all invoices that are still in time slot to pay? That means now() is between date and

knubbe's avatar
knubbe's avatar tykusLaravel
7

How to merge two query into a single query in Laravel?

In my application, I have two similar pages where one page has data with group by and another page has without group by. Here is the code for without group by $posts = Product::where('category_id', null)->get(); Here is the code with group by $posts = Product::where('category_id','!=', null)->get()->groupBy('category_id'); When group by I need extra two value for c

prince69's avatar
prince69's avatar tisuchiLaravel
3

Laravel Scout with Typesense and query with

When using the search method and paginate for my data, data is returned correctly with correct total number of records. When i include the query method then i get 1 page of data and an incorrect total count. return $this->model->search($search) ->query(fn(Builder $query) => $query->with('mainListingImage')) ->options([

jackkitley's avatar
jackkitley's avatar jackkitleyGeneral
0

Query builder on filament resource

hi everyone, i am having an issue using query builder for a filament table resource on page LearnersRource page generated using filament: public static function table(Table $table): Table { return $table ->columns([ //All table columns and data ]) ->query(function (Builder $query){ //$user = Auth::user(); $user = auth('web')->user();

ofureigbelos-9707055's avatar
ofureigbelos-9707055's avatar ofureigbel...Laravel
2

Getting a yellow squiggly line and an error but my query works as expected

I have this controller method : public function showByCategory(Request $request, $categorySlug) { $category = Category::where('name', $categorySlug)->firstOrFail(); $listings = Listing::whereHas('item', function ($query) use ($category) { $query->whereHas('sub_category', function ($subQuery) use ($category) { $subQuery-

thetanaz's avatar
thetanaz's avatar thetanazEloquent
11

Passing Query to Loop on Blade with viewData

Can you help me regarding the filament where I want to display a table that comes from a blade, I put this component in the Action->infolist, to display a table whose data is a query, if you use ->viewData['working_hours' => WorkingHours: :all() ] is successful, but if you use state or record an error still occurs, here is the component that passes it to the blade Text

iphai's avatar
iphai's avatar iphaiFilament
0

Query returns wrong value

I'm trying to validate an excel file, in this case I'm trying to search between the excel row $data and the system information to verify it's correct. Here I'm trying to find districts, these districts have a chained relationship like this country->province->canton->district. What is happening is that I have multiple districts with the same exact name but different can

eggplantSword's avatar
eggplantSword's avatar msslgomezGeneral
3

How to create a table with one unique column at the same time with one query?

How to create a table with one unique column with the same sql query? That is, with one query. I wrote this code: Schema::disableForeignKeyConstraints(); Schema::table('orders', function (Blueprint $table): void { $table->id(); $table->unsignedBigInteger('order_number')->unique(); }); Schema::enableForeignKeyConstraints(); But this is executed in the two queries. Fi

nekooee's avatar
nekooee's avatar Tray2Laravel
3

Use MySQL query results as a table in Filament

Hi All I have this MySQL query in a PHP page: $sql = "SELECT jobindex,Silo0, NewWeight, OrderNumber, R_Type, R_Manufacturer, RawMatWeightActual, DensityMean, Comment, DateTimeStop FROM silo_update WHERE Silo0 NOT LIKE '-----' AND RawMatWeightActual<> 0 AND jobindex IN ( SELECT MAX(jobindex) FROM silo_update GROUP BY Silo0) ORDER BY LENGTH(Silo0), Silo0"; I used

LawrenceSmith's avatar
LawrenceSmith's avatar Tray2Filament
2

Rewrite my query to equalent

Hi! I have not created this query as equalent query. Is there som good equalent tutorials out there ? Her is my query; $query = DB::table('gpstrackers') ->select('gpstrackers.*', 'users.name') ->leftJoin('users', 'gpstrackers.player_id_ref', '=', 'users.id') ->whereIn('gpstrackers.id', function ($query) use ($from1, $to1, $golfID, $courceid, $status) {

birdietorerik's avatar
birdietorerik's avatar BeiriLaravel
5

Laravel query not returning correct values

Hi. I am building an web app using Laravel and Vue. I have a view with a table showing expenses by category. However filter is not working. async getCategories() { this.loading = true; const { response } = await this.request('GET', '/get-other-categories', { params: { userId: this.activeUser.id, ...this.filters } }); this.categ

jazepsbumbiers's avatar
jazepsbumbiers's avatar jazepsbumb...Laravel
2

How can I add query params to url without re-render

Hi guys! on input change I want to add the input parameter in url query without having to refresh the entire page, is there any way to do it? I'm using React with inertia. Current code uses inertia that causes re-render: router.get(route('map'), queryParams)

gambino's avatar
gambino's avatar DhPandyaInertia
1

Laravel Filter Search Query Builder

Hey Everyone, I'm looking for filter search query builder. check the below link for reference but it's paid. Any similar package or project available for free so I can implement that into my application. I'm trying to generate reports for orders, products, expenses. right now I statically added column and logic, but end users expecting more advanced. So pls any one share your s

rajkumar_zuan's avatar
rajkumar_zuan's avatar jlrdwLaravel
1

Invalid parameter number using raw query, BUT THE CORRECT # OF PARAMS ARE BOUND

I'm receiving an error when trying to run a SQL statement with whereRaw() Error: Invalid parameter number MyModel::whereRaw("status_id_one = :statusId OR status_id_two = :statusId",[':statusId' => $statusId])->count(); :statusId is the only value bound in the query two times same value.. why isn't this working?

laraguy25's avatar
laraguy25's avatar JussiManni...Eloquent
4

Query Builder: I want to add up the votes of the same person

Here is my SQL query: $votes = DB::table('options') ->select('id', 'poll_id', 'option_text', 'votes', 'created_at', 'updated_at') ->get(); I want to add up the votes of the same person (for example "Le Pen") "Le pen" is an option_text. See the screencapture hereafter for better understanding: https://i.gyazo.com/583a151077c5a725381a81443f362bf5.png

ilex01's avatar
ilex01's avatar ilex01Laravel
1

Laravel Multi-tenant database, query multiple databases at the same time

I have a multi-tenanted laravel app with separate database connections: config.database.php '4D_VIC_HUB' => [ 'driver' => 'mysql', 'host' => env('4D_DB_HOST'), 'port' => env('4D_DB_PORT'), 'database' => '4D_VIC_HUB', 'username' => env('4D_DB_USERNAME'), 'password' => env('4D_DB_

andrei.storman's avatar
andrei.storman's avatar Tray2Eloquent
2

SQL Query To Eloquent Relationship

// category-product_variant pivot table Schema::create('category_product_variant', function (Blueprint $table) { $table->id(); $table->foreignId('category_id')->constrained('categories'); $table->foreignId('product_variant_id')->constrained('product_variants'); $table->timestamps(); $table->softDeletes(); // unique constraints $

croftCoder's avatar
croftCoder's avatar Tray2Laravel
7

Query Builder | whereNotExists()

I'm trying to run a simple conditional Insert, but the query keeps on adding the row to the table even though the condition should not allow it. Can you please check if am I missing anything? Thanks laravel 11, no errors. DB::table('my_table') ->wherenotExists(function (Builder $query) { $query->select('id', 'fname')

alex32's avatar
alex32's avatar alex32Laravel
7

Query to database very long

Hello, I have a table with 600 000 lines. I need to retrieve the last 10 lines (created_at) every second. The query takes about 10 seconds to be executed from the application. When I execute the same query directly inside phpmyadmin, it takes about 3 to 5 seconds. The table has only 4 fields : id, content, created_at, updated_at. The created_at field is indexed. What could be t

vincent15000's avatar
vincent15000's avatar vincent150...General
16

Help diagnosing slow Eloquent query

// Laravel 9 I'm querying against a table that has a couple million records with a _raw field that is indexed for searching. I have a hook in my AppServiceProvider that logs sql queries so I can inspect the format and performance. DB::listen(function ($query) { Log::channel('sql')->info('query_log', [ 'query' => Str::replaceArray( '?',

sloan58's avatar
sloan58's avatar LaryAIEloquent
1