Transforming API query string to eloquent where query

Hello guys/girls. I solved one problem but not fully satisfied with it. I would love to make it a one liner but I cannot achieve this alone. From browser i'm receiving this ?foo[eq]=bar&department[eq]=06&date[eq]=2021-13-12 Illuminate\Http\Request $request->query() will transform it to ['foo' => [ 'eq' => 'bar' ], 'department' => [ 'eq' => '06 ], 'date'

tyteck's avatar
tyteck's avatar tyteckLaravel
4

QUery with 2 right joins make duplicate values

Hello i have this code that works well unless i add different value to the same existing user, this is the query: $userId = auth()->id(); $contactsAssignedCars = CarBorrow::query() ->select([ 'users.id as id', 'users.first_name as first_name', 'users.last_name as last_name', DB::raw(&quo

devkon98's avatar
devkon98's avatar LaryAILaravel
1

How can I further optimize this query or is there any other alternative?

I have a table with 37 million records (11 million duplicates) and I want to delete all the duplicate records based on three columns. Currently I have written a command to do so as below in laravel 7- $oldUserStatsQuery = DB::table('user_stats_data') ->select('user_stats_data.id') ->leftJoin( DB::raw( '(SELECT MAX(id) AS id

rishavjain_25's avatar
rishavjain_25's avatar LaryAIEloquent
1

How to use query bindings for single quoted queries?

Hello, How to use query bindings for single quoted queries? $path = DB::scalar("SELECT ST_AsEncodedPolyline(GeomFromEWKT('SRID=4326;LINESTRING(?,?,?)'))",[ '-120.2 38.5', '-120.95 40.7', '-126.453 43.252', ]); Illuminate\Database\QueryException SQLSTATE[08P01]: <>: 7 ERROR: bind message supplies 3 parameters, but prepared statement "pdo_stmt_00

mgsmus's avatar
mgsmus's avatar mgsmusLaravel
2

How to get specific column data from raw query result?

Hello, I have a raw select: $data = DB::select('...raw query...'); which returns an array of arrays: [ 0 => ['col1' => 'col1_data1', 'col2' => 'col2_data1'], 1 => ['col1' => 'col1_data2', 'col2' => 'col2_data2'], 2 => ['col1' => 'col1_data3', 'col2' => 'col2_data3'] ] How can I "extract" each column individually? For example if I wan

Ligonsker's avatar
Ligonsker's avatar overdr1veCode Review
3

Google Drive query examples

Does anyone have any examples of how to use Google Drive query parameters with flysystem? In particular, accessing a folder 'Shared With Me'

Snapey's avatar
Snapey's avatar SnapeyGeneral
2

select in relation with with group by query

guys how to select in relation with group by id. i have query like this $chart = Pesanan::with(['kendaraan' => function($query){$query->select('id','jenis')->groupBy('id');}])->where('petugas_approve', 'approved')->Where('akomodasi_approve','approved')->get(); and i use return $chart; and the result showing many data with same id. i want the data showing onl

bcoder72's avatar
bcoder72's avatar tykusLaravel
3

DB statement throws an error in migration while running the MySQL query in the console works fine

I am trying to run the following query in a migration: UPDATE `notifications` SET `object_type`=CONCAT('App\\Models\\', object_type) WHERE `object_type` NOT LIKE 'App\\\\Models\\\\%'; It works fine in the console. But when I am using it in the migration, it throws an error. DB::statement("UPDATE `notifications` SET `object_type`=CONCAT('App\\Models\\', object_type) WHERE

debiprasad's avatar
debiprasad's avatar debiprasadLaravel
2

MySQL: Escaping backslash in the LIKE statement works in SELECT query but does not work in UPDATE query

When I run the following query: SELECT * FROM notifications WHERE `object_type` LIKE 'App\\\\Models\\\\%'; it works fine. But when I run the following query: UPDATE `sss_notifications` SET `object_type`=CONCAT('App\Models\', object_type) WHERE `object_type` NOT LIKE 'App\\\\Models\\\\%'; I get the following error: ERROR: Unknown command '\\'. ERROR: Unknown command '\\'. ERRO

debiprasad's avatar
debiprasad's avatar debiprasadGeneral
1

Eloquent Query

Hello. I've been struggling with some querys that works as expected unless has where clauses. For instance I'm trying to filter clients that belongs to the current user. When there's no filter works fine, but when filter is submited shows all records that matches the query even if the user is not the owner. Is there something wrong? public function index(Request $request) { $fi

mfdzmirabal's avatar
mfdzmirabal's avatar mfdzmiraba...Laravel
2

Help with sorting query for pagination

I have a multi relation query that needs to be sorted by a deep relation and can not seem to get it going. Model relations: Listing belongsTo Subscription Subscription hasMany Listing Subscription belongsTo Plan So I query all the Listing(s) that have an active Subscription and the Subscription includes the Plan but i cant get the Listing(s) that are subscribed to higher / lowe

movepixels's avatar
movepixels's avatar LaryAILaravel
1

Named parameter is not working for Query Scope parameters

In my User model I wrote this query scope with named parameters but seems like it doesn't return anything when I tinker it public function scopeActive(Builder $query): void { $query->where(column: 'is_active', value: true); } But, without using any named parameters, it just works fine! But why? $query->where('is_active', true); //btw I have PHP 8.1.x

alnahian2003's avatar
alnahian2003's avatar alnahian20...Eloquent
2

Form request custom rule that requires DB query

Hello, In some page I have an input that I need to match against some query result and make sure that the input from the user is not greater than the result of the DB query (I will do SUM of some column). For such validation rule, what should I use in the Form Request? Should I create a new rule and call it something like ValidCustomInputRule and do the DB query there, or there

Ligonsker's avatar
Ligonsker's avatar LaryAICode Review
1

DB::table() query doubt

Hi friends can you please guide how to write the sql query below in laravel DB::table() facade. UPDATE exam_application_fees AS ExamFees, students AS Student SET ExamFees.fine_amount=100, ExamFees.total_amount = ExamFees.total_amount + 100 WHERE student_id NOT IN ( SELECT student_id FROM payments WHERE exammonth_id = 3 ) AND ExamFees.exammonth_id = 3 AND

vidhyaprakash85's avatar
vidhyaprakash85's avatar PippoGeneral
3

Complex Query with calculation

Hello, I need help with building a complex query (well, complex for me at least). I have the following Models with relationships: User -- (hasMany) --> Items -- (hasOne) --> Inspection What I need to do: Show a count of all Inspections for each User after doing a simple date calculation on every Inspection to sort it into one of three statuses. So the result could look li

cantoo's avatar
cantoo's avatar cantooLaravel
0

optimize the query on jquery interval

i have a function like below, public function checkClassLiveDiscussionSession(Request $request, $course_menu_name, $topicslug, $slug) { $coursedetail = $request->attributes->get('coursedet'); $purchasedpackages = $request->attributes->get('orderCheck'); $topicdetail = Topics::where('slug', $topicslug)->where('active_status', 1)->wh

Deekshith's avatar
Deekshith's avatar LaryAILaravel
1

Pagination and query too slow on server

I have get all users method that shows all users from database with pagination. On local everything works fine and fast but thats probably because I don't have many users in database. But when I go to server it takes one minute to open up get all users page and when I scroll down it takes same amount of time to opet ?page=2 and etc. Query calculates distance from other users b

dr24's avatar
dr24's avatar dr24Laravel
2

What's the best way to write a different query for each user role ?

Hello, I work on an application where there are teams with users having different roles. If a user wants to see some data, the query has to be different according to the role. What's the best way to do that ? Is it for example writing scopes ? I thought about a specific scope for each role, for example scopeFilterForManager(), so that I can have a unique bae query and then filt

vincent15000's avatar
vincent15000's avatar PippoEloquent
5

Pass search query parameters to Laravel Excel from Livewire component

How do I pass model search query parameters from Livewire component to Laravel Excel route ? Livewire component: public $search = ''; public function render() { 'posts' =>Post::where('description', 'like', '%'.$this->search.'%') ->get() } Laravel Excel route inside same Livewire component: public function export() { return Excel::download(n

FireBlade's avatar
FireBlade's avatar FireBladeLivewire
1

Parameter other db in query

i have problem for query in laravel. i have query like this in controller $idbil = Msmpn::where('ID BILL','=', $request->idbil)->get(); when i input the data idbil it showing DB data and i want to make other query with parameter in data $idbil its like this $pajak = Mskomap::select('SKTPOT')->where('KOMAP','=', $idbil->AKUN)->get(); and it showed Property [AKUN]

bcoder72's avatar
bcoder72's avatar vitaly-ero...Laravel
4

how can i code refactor and optimize laravel query

how can I refactor the below code and optimize the query of the Database? $times = SampleTime::all(); $now = Carbon::now()->timezone('My/timezone')->format('H:i'); $times->each(function ($time) use ($now) { $diff = Carbon::parse($time->time)->diffInMinutes($now); $this->alert($diff); if ($diff <= 15) { $roleIds = Role::select('id')

pouyabh's avatar
pouyabh's avatar SnapeyLaravel
2

Sub Query of User detail not working

I have News Articles created by users. The page gets the list of all the News with the IDs of the authors. The problem is, the details requested by the sub query for the authors returns null. GraphQL Query newsByPage( first: $first page: $page ){ data{ id title body user_id{ id

emexrevolarter's avatar
emexrevolarter's avatar emexrevola...Eloquent
8

Virtual rows on a query

HI im struggling to find a solution , I run a query that fetching shifts that has $shift->start_time and $shift->end_time when that fields are not null then we have a real shift sort by date like this screenshot: https://prnt.sc/u7z2cTgxHYRb As you can see in that rows is the field start_time you see the missing dates like 1/03/2023, 2/03/2023 , 3/03/2023 So want to ins

Chris1989's avatar
Chris1989's avatar webrobertEloquent
2

Query Issue Laravel

I have this many to many (campaign_events), in this table I have these records +----+----------+-------------+----------+ | id | event_id | campaign_id | group_id | +----+----------+-------------+----------+ | 11 | 2 | 1 | 1 | | 12 | 2 | 1 | 3 | +----+----------+-------------+----------+ I created the relationship in the Campaign

musti132's avatar
musti132's avatar musti132Laravel
2

Where to chane query

Hi! I am using auth()->user() many places in my application Want to add a colum to query that auth()->user() returns (need to join another table in the query, that return a colum i want) Where do you change the query for auth() ?

birdietorerik's avatar
birdietorerik's avatar LaryAILaravel
1

Laravel Unnecessary query

Hello, I have a FormRequest validation which has the exists validation like so 'event' => ['required', 'string', 'max:255', 'exists:events,slug'], Because of the exists validation the app will run a query to check if the 'event' exists in the database, in the controller is there anyway I can access the query results (if it exists), instead of running another query like so $

musti132's avatar
musti132's avatar musti132Laravel
3

Nested Query Question

Hello, I have a three Eloquent Models which are Linked like this: A HasMany B HasMany C And I want to get all A's which have B's where the latest C is older than lets say 30 day. There are multiple C's for a B and I am only interested in the latest (via created_at column) Does anyone know how to fetch this data with a single SQL Statement? I tried something like this but I don'

markus.heb's avatar
markus.heb's avatar markus.hebEloquent
3

Query unexpected behaviour

I'm using Inertia as well this particularly is a partial reload but I'm not sure that is the problem so I tagged the questions as Eloquent instead. This is the query in question, what happens is when I call it in one spot it'll say it counts 3 items but later when I try to get it I only get one result. I removed of lot of other code to just show this one query and every time it

eggplantSword's avatar
eggplantSword's avatar LaryAIEloquent
1

MySQL and DB facade query difference

I have a laravel db facade statement DB::table("internal_marks AS InternalMark")->select('Student.registerno', 'Student.name', 'Subject.subject_code', 'Subject.subject_name') ->whereNotIn('InternalMark.id', function ($q) { $q->select('internalmarks_id')->from('internal_mark_entries')->whereNotIn('internal_mark_entries.entry_i

vidhyaprakash85's avatar
vidhyaprakash85's avatar LaryAILaravel
1

when query not seeing variable with array

Hi All I've got an $options variable that is sometimes populated with an array... when its not null i would like the query to find the matching array of ID's but currently its throwing an undefined $options variable error. $options = LineItem::where('category_id', '2')->pluck('option_id'); ... ->when($options, function($query) { return $query->whereIn('line_items.o

ollie_123's avatar
ollie_123's avatar ollie_123Laravel
2

Any way to detect the type of sql query being fired by the user?

Hey Everybody, I am workin on a project where I am taking sql query as input from the user and returning the results of it. Is there any way to check if the query being submitted is a "select" or an "insert/delete/update" etc? Any help is very appreciated.

madprabh's avatar
madprabh's avatar madprabhLaravel
6

MYSQL complex query joins

i have students table with following information. Students id, registerno, name Internal marks table Student Internal Mark is information is added here because it may contain one or more entries id, student_id subject_id Internal marks entries table In this for internal mark totally 3 entries will be made (entry 1, entry 2 and entry 3) id, internalmarkentry_id entryno_id tota

vidhyaprakash85's avatar
vidhyaprakash85's avatar vidhyaprak...General
7

Query builder "strict" whereIn, or why multiple where not working.

Hello, i can't buld a query, which make "strict" whereIn request. I have polymorphic manyToMany scheme, with categories , documents and categoryables tables. Now i have some correct data in categoriables table: id category_id categoryable_type categoryable_id 26 4 "App\Models\Document" 7 27 5 "App\Models\Document" 7 28 4 "App\Models\Document&

Fomaxwebcreator's avatar
Fomaxwebcreator's avatar robgordijnEloquent
5

Adding select() breaks Eloquent query

I have two tables inventory_items and medications that both have a name column. I'm creating a query builder and need to alias the name column on medications. I've built my query, but if I add the select() method, it fails to load the related model. When the select() method is added to the builder, this is what I see in the DB log for the related model query: //fails to loa

robinsonryan's avatar
robinsonryan's avatar robinsonry...Eloquent
2

query throws error on testing but works on browser

I use pestphp for my test suite and I have this query in a controller method $conversation = Conversation::query() ->with(['groups', 'groups.contacts']) ->selectRaw('*, MATCH (hear) AGAINST (? IN NATURAL LANGUAGE MODE) AS score', [$hear]) ->whereRaw('MATCH (hear) AGAINST (? IN NATURAL LANGUAGE MODE)', [$hear]) -&g

sh1r3f's avatar
sh1r3f's avatar sh1r3fTesting
3

Mistake with get with Query params

Hello I'm running into a problem that I can't see. I am doing a test of an endpoint that works perfectly. I use Laravel 10 (update yesterday) and Spatie/QueryBuilder. My Controller CommandCenterApiController public function index(CommandCenterFilterRequest $request) { ray($request->all())->die(); // Show empty array // More code } Well, if I run the query {{local}}

abkrim's avatar
abkrim's avatar abkrimTesting
0

Trying to change the following sql query in laravel

I am trying to convert the following sql query : SELECT state, county, COUNT(1), (COUNT(1)*200) AS score FROM lawyer_subscribers WHERE id IN (SELECT subscriber_id FROM lawyer_subscriber_case_types WHERE casetype IN ('Family Law')) AND status = 1 GROUP BY state, county HAVING (COUNT(1)*200) >= 200 AND (COUNT(1)*200) &l

ankur_g's avatar
ankur_g's avatar ankur_gLaravel
0

laravel optimize query with cursor or chunk

Hello everyone i used to optimize query using cursor method but i dont know how to implement cursor in wherehas query so please suggest anyone how to use it foreach(Product::cursor() as $product){ $product->whereHas('supplier', function (Builder $query) { $query->where('sys_state','!=','-1'); }) ->whereHas('customer', funct

vandan's avatar
vandan's avatar vandanLaravel
5

Trying to change the a sql query in laravel 9

I am trying to convert the following sql query: SELECT state , county , COUNT(1), (COUNT(1)*200) AS score FROM lawyer_subscribers LEFT JOIN lawyer_subscriber_case_types ON lawyer_subscribers.id = lawyer_subscriber_case_types.subscriber_id WHERE casetype = 'Family Law' GROUP BY state , county ORDER BY state , county; I have tried it in laravel 9 as following: public function su

ankur_g's avatar
ankur_g's avatar ankur_gLaravel
1

Probably Simple Query to be done

I am still learning. At the moment though I just need what I consider a simple. Single Table: task sign leg_size Sample Data would be: 1111 5050 pole 1111 5151 tpost 2222 4234 pole I want to display all the data as such: task #: 1111 -- Sign #: 5050 -- Leg Size: Pole -- Sign #: 5151 -- Leg Size: tpost task #: 2222 -- Sign #: 4234 -- Leg Size: pole Currently Tried: DB::tab

bitphire's avatar
bitphire's avatar bitphireLaravel
4

What is wrong with this eloquent query?

I've this eloquent query $conversation = Conversation::query() ->with(['groups', 'groups.contacts']) ->selectRaw("*, MATCH (hear) AGAINST ('?' IN NATURAL LANGUAGE MODE) AS score", [$hear]) ->whereRaw("MATCH (hear) AGAINST ('?' IN NATURAL LANGUAGE MODE)", [$hear]) ->havingRaw('score > ?',

sh1r3f's avatar
sh1r3f's avatar rodrigo.pe...Eloquent
2

Can't Query Database With MS SQL Read Only Account?

Hello! I am developing an internal app where the plan was to have 2 MS SQL 2016 database connections. One is our main "app" database and the other database is a LOB application from another vendor we don't want to mess with. The main app connection works great without any issues. The LOB database is mission critical and we can't risk a 3rd party app messing with the d

mr_reboot's avatar
mr_reboot's avatar mr_rebootLaravel
5

Is it a good idea to query once and split the collection of results? And if so, how to do it based on relationship?

Hello everyone, I'm wondering if there is benefits into querying once and then splitting the results, and if so, how to efficiently do it? For example, let's say that in the application there are 2 models: User and Post. a User hasMany posts. The Post model has a published_at property, which can be a day in the future. If I want to show a table with users and their posts that w

fholet's avatar
fholet's avatar MohamedTam...Laravel
2

Map on query not working as expected, ignoring variable

This is my query Team::with('user', 'teamUsers.user:id,name')->whereHas('teamUsers.route')->get() ->map(function($item) { $return[] = ['value' => $item->user->id, 'label' => $item->user->name]; $item->teamUsers->each(function ($v) use ($return) { //problem here $return[] = ['value

eggplantSword's avatar
eggplantSword's avatar webrobertEloquent
4

Search across relational table with a custom query builder giving null

I have a table purchases and another table purchase_company_details and I want to search for data in get record where either of them have the data. But I am getting null if I give a vary related data to either oi column. e.g: I am searching data in po_no column in purchase table and name column in purchase_company_details. Now if I search for PO, I get data as something in na

princeoo7's avatar
princeoo7's avatar princeoo7Laravel
3

multiple where query

Hi, I'm new to laravel and have only been programming for 6 months. I've been able to make some great progress thanks to Laracast but I've run into an issue that I can not seem to resolve. So I'm hoping one of you can tell me why this is not returning the expected result Within my livewire dashboard class I'm calling the FilterAds method in my Ad Model. This method filters the

rfb's avatar
rfb's avatar rfbLaravel
19

Why does Query Builder not allow named parameters?

This may be an unanswerable question lol, but does anybody know why Query builder is designed to not allow named query parameters??! I was trying to get them to work for a while, then saw this code in Illuminate/Database/Query/Builder.php public function getBindings() { return Arr::flatten($this->bindings); } If I give bindings such as [

dougd_nc's avatar
dougd_nc's avatar dougd_ncLaravel
8

Merge records in one query

I want to merge records in on query. This yyy | zzz | 12 | 12.00 | 12.00 yyy | zzz | 12 | 12.00 | 12.00 Should be yyy | zzz | 12 | 12.00 | 24.00 Is this possible?

lara28580's avatar
lara28580's avatar hamzaaslamLaravel
5

simple query is too slow 645.63ms <->1500.00ms

Hey, I'm facing performance issue due to below query. select * from "res_users" where "id" = 2 limit 1 Note: res_users contians only two rows Telescope Query Details Location: public\index.php:52 Duration: 645.63ms Tags: slow Environment Laravel Framework: 9.19 Database : PostgreSQL hosted on Amazon RDS

alhawaj's avatar
alhawaj's avatar alhawajLaravel
6

Redis Cache for Database Query Performance

Hi, what is the best way to uplift database query performance using redis in lumen

arunasrim's avatar
arunasrim's avatar JussiManni...Lumen
5