Query and fetch polymorphic table realted relations in Laravel

I have the following tables payments --------- id, user_id, payable_id, payable_type, status Payable can be of type User Subscriptions / Courses Registered and something else too course_categories --------------- id, name, slug courses ------- id, name, currency_id, price, course_category_id, language_id I want to query all the payments made by login user and with status suc

channaveer's avatar
channaveer's avatar channaveerEloquent
2

Softdelete query get results in Join query

Hello, I have applied softDelete in Bill model and File model as well. But when we use SQL join, it also gets deleted items that have deleted_at timestamp, $bills = Bill::where('is_bill', 1) ->join('files', 'files.bill_id', '=', 'bills.id') ->get() ->unique('bill_id'); i must need to use ->where('files.deleted_at', NULL) to remo

LaCoder's avatar
LaCoder's avatar bobbybouwm...Eloquent
1

Why does this query in model not working ?

Hi! Have this query thats working fine: $gpsdata = DB::select('SELECT flightnumber, MAX(id) AS id FROM gpstrackerclubs GROUP BY flightnumber DESC;'); But if i add a new colum like : $gpsdata = DB::select('SELECT flightnumber,timeused, MAX(id) AS id FROM gpstrackerclubs GROUP BY flightnumber DESC;'); Then it not working ? Gives me this error "SQLSTATE[42000]: Syntax error o

birdietorerik's avatar
birdietorerik's avatar s4muelLaravel
8

Spatie Query Builder ignores my with

I'm using using Spaties LaravelQueryBuilder to allow for some additional filtering on my api. I've got a model called 'AvailableIntegration' and this model has a relationship through to Category that is a belongsToMany; Without QueryBuilder I can make the following query $availableIntegrations = QueryBuilder::for(AvailableIntegration::class) ->allowedIncludes(['c

automica's avatar
automica's avatar automicaLaravel
5

Order a query by date from two different tables

Hey guys! I would like to order my query by field date from two different tables. I have a Model Report with date. One report has (or not) one event with date. If a report has an event, the report date is null. So it only exists one date, or from report or from event. Our structure is something like this: Report: { // no date, but event with date "event": { "dat

dani94's avatar
dani94's avatar dani94Eloquent
4

Database: Query Builder

Hey guys i'am trying to update my db_name but to prevent error's i'am trying Database: Query Builder i'am lost can someone help me please? Thank you! public function handle() { $data = DB::table('users') ->select('id','name') ->where('db_name', null) ->limit(10); // ->update(['db_name' => DB::raw("CONCAT(name,

bobdebower's avatar
bobdebower's avatar bobdebowerLaravel
13

Help in converting query to eloquent

I need some help in converting this query into eloquent equivalent. The model in which the query is to be executed is named as Inventory. This field should be appended to the column list. What I am trying to achieve is, say, I already have columns a & b in Inventory and I want to append avl_quantity to it. Writing Inventory::withAvailableQuantity()->get() should fetch th

ehsanquddusi's avatar
ehsanquddusi's avatar orestEloquent
9

Accessing related model in global query scope

Hey everybody! I'm working on a global query scope, where I need to access a related model and run some checks on it. I have projects and comments on those projects. Let's say my route is /projects/{project}/comments and my relationship looks like this on the project model: public function comments() { return $this->hasMany(Comment::class); } Now, whenever I load the co

Ahrengot's avatar
Ahrengot's avatar AhrengotCode Review
1

Query builder with with() to retrieve relationships

Hello, I try for hours to understand why the $trainingId is not filtering the courses. public function listByTraining($trainingId) { $trainingId = 8; // I even added this here to verify $courses = Course:: with(['teachingUnitSubject' => function($query) use($trainingId) { $query ->with(['teachingUnit' => function($query) use($tr

vincent15000's avatar
vincent15000's avatar vincent150...Eloquent
6

Query inputs in from_date and to_date

I have two inputs in form from_date and to_date, I also have two fields in database from_date and to_date, I want to query the records using input from_date and to_date, I want to query data from database where the inputs are in between from_date and to_date of database. $from_date = $request->input('from_date'); $to_date = $request->input('to_date'); $absentees = Absent

shami003's avatar
shami003's avatar shami003Laravel
8

Query optimization

Hello, hope you are doing good! I have more than 4500 businesses in my database. Each business hasMany tags. So I have my main search input in navbar. When user submits any string via that search input I want to display all businesses that contain this string in any of their tags or/and in it's name field. Example: When string = ab. It must display first 20 businesses that con

abdulaziz's avatar
abdulaziz's avatar SilenceBri...Eloquent
2

ORM Query Optimization

how can i optimize the query Model:App\Category=>Relation:App\SubCategory you should add with("App\SubCategory") to eager load this relation App\SubCategory=>Relation:App\Product you should add with("App\Product") to eager load this relation controller $category=Category::with(['subCategory','subCategory.products' ])->get(); view file @foreach

freemium's avatar
freemium's avatar SnapeyEloquent
9

How to merge a collection and a query builder in laravel then use paginate

I have +30K items in my requests table. The problem is that every user does not have permission to see all the requests and it's based on a lot of factors which i made a function that returns whether the user have permission to see such a request or not. The problem is that the requests in process i have to pass every request in process to the function to get the permission ( p

achrafoo's avatar
achrafoo's avatar achrafooLaravel
0

Wrong query when make join

Hello Guy's! I have a problem with packages spatie/Laravel-query-builder. I used this package for easy way to filter and sort my query but it's not like that :D I trying to filter result who have two relation - shop and employee. In short, it wants to filter a list of store reports Look, this is my code. When I use join method then in response receives data with incorrect ID. W

Sinres's avatar
Sinres's avatar jlrdwLaravel
7

N+1 query throught multiple tables

Hi all, I'm having a N+1 query issue between a relationship of 3 tables. [2021-06-01 10:41:02] local.INFO: Detected N+1 Query [2021-06-01 10:41:02] local.INFO: Model: App\User Relation: App\Dealer Num-Called: 13 Call-Stack: I've got a table called User and a Dealer table. The relationship between both is AuthorisedDealers with the following structure: id user_id dealer_id cr

j.avi's avatar
j.avi's avatar j.aviEloquent
4

Database query on every view

My app doesn't have authentication but still i see the following query being executed on every view when debugging with Clockwork SELECT * FROM `users` WHERE `id` = '1' LIMIT 1 Any idea where that query is coming from.I suppose it may be a laravel middleware

Th3apprenTis's avatar
Th3apprenTis's avatar SnapeyLaravel
4

Laravel update query (Add a value to existing a value)

How we update value to add exiting value in laravel suppose we have fee column in table we update in first time it value will be 1000 then next time we update value 500 it will be sum =1000+500=1500 i use this query $pay->paying = $request->paying += 500; but it update only fix value 500 we update using form

abhishek88's avatar
abhishek88's avatar amirkamiziLaravel
2

Need help cleaning up complicated query

The query I am having trouble with is getting a monthly sum of the unit_cases by recipe per scenario. I have a Forecast model that every-time I generate it I create 24 Unit_Cases models (24 months and a case value for each) Schema::create('unit_cases', function (Blueprint $table) { $table->id(); $table->foreignId('forecast_id'); $table-

chrisgrim's avatar
chrisgrim's avatar chrisgrimLaravel
7

Query to retrieve an array with the primary key as the array key ?

Hello, I try to write a query to retrieve an array where the array key is the primary key of each element of the query result. Here is my query. $teachingUnit = TeachingUnit::with('subjects')->find($id); $teachingUnit->subjectIds = $teachingUnit->subjects()->pluck('subject_id'); return $teachingUnit; And here is the result of my query. { "id":8, &

vincent15000's avatar
vincent15000's avatar vincent150...Eloquent
7

without global scope of a relation per specific query

Hi Devs, how can I get courses without the global scope only on this specific query unlike implementing on the model level? $user = $user->user()->with('instructor', 'instructor.order', 'courses', 'courses.review', 'courses.category')->first();

Wakanda's avatar
Wakanda's avatar WakandaLaravel
4

CASE SQL query not working in test suite

CASE SQL query not working in test suite

kritika07's avatar
kritika07's avatar kritika07Testing
3

Stored session data on query

hi so i stored the data on my session but i want to implement it like a filter. So instead of showing all data from my query, i want to filter some attribute with the data stored in my session. How do i do such things? since the query is separated from the controller itself? Currently i have this public function testSuiteDetails($suite_id) { return DB::table('test_executions as

rafaeladi's avatar
rafaeladi's avatar rafaeladiLaravel
0

Query with() retrieving only an array of ids from the relationship

Hello, I have a many-to-many relationship and I'd like to retrieve the items of the relationship like this. $trainer = Trainer::with(['skills' => function($query) { $query->select('id'); }])->find($id); I retrieve this ... "skills":[{"id":1,"pivot":{"trainer_id":8,"skill_id":1}},{"id":9,"pivot":

vincent15000's avatar
vincent15000's avatar SnapeyEloquent
9

Performance btw Laravel Query Builder and SQL

I have a DB view called 'barcode_filter' Calling a select query using Query Builder $menu = DB::table('barcode_filter')->select('short_descip','short_descip1', 'retail1', 'barcode','dept_code','tax_perc', 'tax_flag','availability','qty_check','recipe_item_code','recipe_qty','preparation_time','discount_amt','discount_flag') ->where('company_code',2)

anand_aks's avatar
anand_aks's avatar anand_aksLaravel
9

Trying pass query from Repository or Model to Controller

The best way to pass query from the model to Controller

paravecdesign's avatar
paravecdesign's avatar paravecdes...Laravel
6

Question about Laravel migration query to MySQL

Hi, I'm trying to migrate an existing Laravel project to MySQL on Vitess. When I tried php artisan migrate, the following error was happened. SQLSTATE[HY000]: General error: 1105 unknown error: syntax error at position 55 near 'users_id_primary' (SQL: alter table users add primary key users_id_primary(id)) The queries sent by Laravel were here; CreateUsersTable: create table `

Yoshi1613's avatar
Yoshi1613's avatar Yoshi1613Laravel
5

Laravel Query whereNull orWhere

Hello, i have to retreive a collection of products where the price is null or under a value. $products = Product::where('envoi_encheres',1) ->where(function ($query) use ($enchere) { $query->whereNull(self::PRIX_MIN) ->orWhere(self::PRIX_MIN, '<=', $enchere->prix); }) But when the product has a price at NULL, the line orWhere(self::PRIX_MIN, '<=', $enchere->

nasmo's avatar
nasmo's avatar sr57Laravel
1

Converting a SQL query into query builder. Select from several tables, Joins, where, when, order by

Hello, I have a SQL query i need help translating into query builder. The query is for a search function, so its two parts. Main part when search input field is empty. Return all result. Second part is all the where clauses when something is typed into search input field. Im using livewire. only need help with the query builder. So Raw SQL is as follows: SELECT t1.date, t1.hour

phingoc's avatar
phingoc's avatar Tray2Code Review
1

Optimization query

Hi, in the application I use queries which relate data from the database. Everything still works, but I would like to optimize those queries because when loading the page, 16 queries and 14 duplicates light up in my debugbar. Could it help do the scope in this case? If so, could you tell me what such a scope should look like? If not, how could I solve it? Thank you very much.

Johny22's avatar
Johny22's avatar Johny22Eloquent
13

Query is giving unexpected data in Eloquent Laravel

My data example is given below: { "id": 2000010, "delivery_name": "", "delivery_mobile": "", "delivery_address": "" }, { "id": 2000007, "delivery_name": "Monjurul Islam", "delivery_mobile": "", "deli

nafeeur10's avatar
nafeeur10's avatar SilenceBri...Laravel
3

I need help to convert this SQL query to Eloquent

This query is meant to get the sum of all adult_rates in the given date range (from start_date to end_date) declare @startdate date, @enddate date set @startdate = '20150101' set @enddate = '20150105' select sum(datediff( day, case when @startdate > start_date then @startdate else start_date end, case when @enddate < end_date then @enddate else end_date

Jroid's avatar
Jroid's avatar JroidEloquent
15

database query select by distinct values

Hello, I would like further develop this query by, selecting only distinct/groupby?? "h_name" and only getting the max calc_mem, whereDate('created_at', '>', $timeperiod), and having('calc_mem', '>', 0.9). $lrgtimes = HDatas::select(DB::raw('calc_mem, h_name, alloc_lim, mem_peak, created_at')) ->whereDate('created_at', '>',

HUNHammer98's avatar
HUNHammer98's avatar sr57General
1

Query filter

Hi, I would like to consult / ask. I wrote a query which I extract data from the database that will come with the request according to the country code and dimensions. Everything works. Is this function written well and correctly? Well thank you. public function getDimensions(SearchDimensionRequest $request) { $dimensions = Country::where('country_code', $request-

Johny22's avatar
Johny22's avatar Johny-22Eloquent
5

Query count aggregate with sum

Hi all, trying to write a query below and saying the transactions_sum_amount isn't a column. Context: A goal has many transactions, amount is per transaction. A goal also has an amount. // Query throws an error request()->user()->goals()->withSum('transactions', 'amount')->having('transactions_sum_amount', '>=', 'amount')->count() // This query works as expec

ChrisxHart's avatar
ChrisxHart's avatar sr57Laravel
8

Query Scope for Relationship

Hello, I have a query which isn't working as I want it to. Product::with(['categories' => function ($categories) { $categories->enabled(); }]) ->with('media') ->get(); If we have a Product with no Categories which are enabled(), I don't want the Product returned. So basically, only

elliotk's avatar
elliotk's avatar elliotkLaravel
2

Typecast from varchar to int while relationship query executed.

I have a delivery table with deliveryNumber as the primary key of varchar 10, due to the special requirement: DeliverySart with "00" and has one to many relationships with Items and Packages. Sql: $order= Delivery::with(['deliveryitems', 'itempackages'])->where('DeliveryNumber', $request->id)->first(); Result query: array:3 [ 0 => array:3 [ "qu

abdulkhan's avatar
abdulkhan's avatar abdulkhanEloquent
3

Ignore Duplicate Rows on Eloquent Query

Hey there, I have a Posts Table, where the Titles of the Posts can be the same. Iwant to query my Posts Table so that it only returns one element for each duplicate row. Example: id: Titles: Content: 1 Test1 some message 2 Test2 some message 3 Test1 some message 4 Test3 some message My desired result: 1 Test1 some message 2 Test2 some message 4 Test3 som

dome's avatar
dome's avatar domeEloquent
3

how to excute this query in laravel?

the query I want to excute in laravel in pure sql SELECT p.id,title,post_content,image_dir,video_dir,p.created_at,first_name,last_name,profile_img FROM users AS u,posts AS p WHERE title like '%mo%' OR post_content like '%mo%' AND user_id=u.id

mohamadAbdelhady's avatar
mohamadAbdelhady's avatar jlrdwLaravel
3

Laravel DB query SUM of two column equals to another table column and count them

I got four tables products, productions, workables and embroidery stock logs. So each production has many morph to product table saved on workables with pivot issued_quantity column. Embroidery stock logs has production_id, product_id, received_embroidery and received_damage. When production is created product_id, workable_id(which is production id) and issued_quantity is inser

imarabinda's avatar
imarabinda's avatar imarabindaLaravel
0

Laravel route multiple parameters on query string

Hello there, I'm new here. I have a route with 2 required parameters and in a case i pass these 2 parameters with 3 other optional variables. When the function builds the route i get an url where in the query string the separator & is replaced by &. What I wrong? How can I solve this? Thank you very much Matteo

matteo.veggiari's avatar
matteo.veggiari's avatar matteo.veg...Laravel
6

Selected or all in eloquent query

Hi, I'm looking for a best way to make an eloquent query where the result is optionally filtered by a dropdown. What I mean is if something is selected then query with just that option, if nothing is selected get all items. My model is a sports club member, but it has relationship with other models - membership, grade, qualification etc. and these will be used as filters also.

redlik's avatar
redlik's avatar PristoMikyEloquent
3

Insert into <table> on duplicate key update select from in query builder?

How (if possible) can I write the following query using Laravel's query builder? INSERT INTO Summary (dy, foo, ct, blah_total) ON DUPLICATE KEY UPDATE ct = ct + VALUE(ct), blah_total = blah_total + VALUE(bt) SELECT DATE(dt) as dy, foo, COUNT(*) as ct, SUM(blah) as bt) FROM Staging GROUP BY 1, 2; S

madsem's avatar
madsem's avatar madsemLaravel
2

Append Relation Count while using select in eloquent query

So i have a query where i want to select specific columns and relation count on a model. Something like this Post::withCount('comments')->select('title', 'content')->get(); Now when I use select in the query comments_count is no longer available. There is $appends option on the model where I can do something like $appends = ['comments_count'] on the Post model but it will

hamidali's avatar
hamidali's avatar Martin Lur...Eloquent
3

Getting only the latest record in query

I have the following query that I can't seem to get only the latest record from. $date = Carbon::createFromDate(2021, 2, 8)->format('Y-m-d H:i:s'); $items = Model::whereDate('published_at', '<', $date) ->whereNull('removed_at') ->whereNotNull('example_id') ->where('filled', false) ->orderBy('example_id', 'asc') ->get(); I need to get

secondman's avatar
secondman's avatar rodrigo.pe...Eloquent
20

How can use an IF/ELSE statement in my SQL query?

SELECT sum(home_score) as C FROM u_games WHERE (home_team_id ='$team_id' OR away_team_id = '$team_id') I need to run one query if $team_id = home_team_id and a different one if $team_id = away_team_id I'm trying to get the total score of a team. But I don't know beforehand whether it's home or away. It's random. Thanks

Daniel1836's avatar
Daniel1836's avatar jlrdwGeneral
4

How to query for category attributes on Laravel?

I am working on a classified site in laravel. I have a list of categories and for each category there are specific attributes. I have seeded the database with data. I am working on the filters of category page. For example for a commercial building i want to filter by no. of bedrooms, no. of bathrooms, is furnished etc. When I try to filter with only single value I am getting r

nitinjohnson's avatar
nitinjohnson's avatar nitinjohns...Laravel
0

Create a query joining three Eloquent models

I have the following SQL query that I need to replicate using Eloquent: SELECT SUM(COL.ORDER_QTY) as UNITS_SOLD, SR.NAME FROM CUSTOMER_ORDER CO JOIN SALES_REP SR ON CO.SALESREP_ID = SR.ID JOIN CUST_ORDER_LINE COL ON CO.ID = COL.CUST_ORDER_ID WHERE COL.PART_ID LIKE 'MODEL%' AND CO.ORDER_DATE > '2018-05-01' GROUP BY SR.NAME ORDER BY UNITS_SOLD DESC I created three Eloquent mo

grozavule's avatar
grozavule's avatar jlrdwEloquent
4

Failing to Cache a Query

I am using this query wrapped in a cache block: $domainProject = Cache::tags("customdomains.{$domain}")->remember("customdomains.{$domain}", now()->addHours(12), function () use ($domain) { return Domain::select('domains.id', 'domains.name') // where has 3 arguments: column, operator, argument to evaluate against ->where('domains.name',

rhand's avatar
rhand's avatar rhandLaravel
11

laravel 8 Query for group and select a sub query and grouped from DB

Hi, in trying to make a query that make a group and works fine but now i'm trying to select from that group the only one that have a count greater than so the groups that have 0 counts doesn't appear ,can anyone help me please? My group query: 'users_int' => Interaction::select('user_id') ->groupByRaw('user_id') ->get() And my try to select the users and i

Daniel-Pablo's avatar
Daniel-Pablo's avatar jlrdwLaravel
1

Query PostgreSQL pg_catalog.pg_statio_all_tables using Query Builder ?

How to convert this query to Query Builder syntax ? SELECT c.ordinal_position, c.table_schema, c.table_name, c.column_name, pgd.description, c.udt_name as udt_name FROM pg_catalog.pg_statio_all_tables as st LEFT JOIN information_schema.columns as c ON c.table_schema=st.schemaname and c.table

devondahon's avatar
devondahon's avatar sr57Laravel
3