Cumulative Query with Laravel Eloquent

Hey guys! I have a products table with a leftover column representing how many products left over. Now I want to write a query to find all remained products until a certain point. id | store_name | product_id | leftover -- | -------------------- | --------------- | ----------- 1 | Store 1 | 1 | 10 2 | Store 2 | 1 | 20 3 | Store 3

hamedghaderi's avatar
hamedghaderi's avatar hamedghade...Eloquent
0

query the relationship and get result according to relation query

hi i want to do a query to get relations of a model and sort the parent model by the query which was done in my relation so like below : $data = Accommodation::with([ 'accommodationRoomsLimited.discount', 'accommodationRoomsLimited', 'accommodationRoomsLimited.roomPricingHistorySearch' => function ($query) use ($from_date, $to_date) { $query->whereDate('fro

farshadf's avatar
farshadf's avatar farshadfLaravel
0

How to do relationship query of a model to a model

Hi, I got 3 models : Configurator, Motor and MotorSize. Motor can be added to Configurator using BelongsTo. MotorSize can be added to Motor using BelongsTo. The relations above works nice (i have added some tables below). The problem is: I need to get all motors that have a MotorSize property/column named "mounting" with a value of "B14" (not sure if the ter

kgp43's avatar
kgp43's avatar kgp43Eloquent
7

Get Query Information before Pagination

Hello, I'm trying to get a few counts on my full ORM query before doing the pagination because it breaks the total results and I need the whole results to make some calculations. Here is part of the code I'm trying to deal with: // Here I get the filtered users by type and situation and paginate the result $users = User::where('type',$type)->where('situation',$situation)->

pdellepiane's avatar
pdellepiane's avatar SnapeyEloquent
3

Eloquent Model query results differently to same plain SQL query (using withCount, whereHas & having)

I have an invoice models which hasMany payments modes, now i want tho collect all Invoices which are paid. For that i need to compare the invoice->totals (=400$) with the sum(payment->amount). $invoices = Invoice::withCount([ 'payments as paymentsSum' => function($query) { $query->select(DB::raw('SUM(amount)')); }

tobias.b's avatar
tobias.b's avatar tobias.bEloquent
2

Refactor huge eloquent query

Hi! I have this HUGE query. I was wondering are there any techniques to refactor queries when they grow too big?

MirasMustimuly's avatar
MirasMustimuly's avatar martinbeanLaravel
2

Possible to make Authentication from a custom query?

First of, here's my issue. I have an Oracle database table Users which I have no control at all. So I would like to use it for authentication only. I get an Inconsistent datatypes error when I try to run this query, select * from users. I didn't dig deep to which column/s is giving me this error as I only need the username and password fields. Thus, when I specify the columns

francisceril's avatar
francisceril's avatar SnapeyGeneral
1

Raw query

Hi, How can I convert this query to Eloquent? SELECT *, (select ga.score FROM game_activities as ga WHERE ga.creator_id = game_activities.creator_id AND ga.type=0 AND ga.game_id = game_activities.game_id) as start_score FROM `game_activities` WHERE game_id=37 and type=2 GROUP BY creator_id Thanks in advanced!

bohowe's avatar
bohowe's avatar bobbybouwm...Eloquent
1

Translate sql to query builder

Hello, i have this raw sql-query: SELECT firstname, lastname, to_char(birthday, 'DD.MM.YYYY') as birthday FROM players WHERE (extract(year from age(birthday)) + 1) * interval '1 year' + birthday between current_date and now() + '60 days' ORDER by EXTRACT(Month FROM birthday), EXTRACT(Day FROM birthday) I have problems to build the select with the query builder.

latz's avatar
latz's avatar bobbybouwm...Eloquent
1

orWhere when query don't give results

Standard search function: $posts = Post::where('title', 'like', "%$query%") ->paginate(5); It's work, but when search dont find query they show blank page. Question is how show string like "No results"

migdalius's avatar
migdalius's avatar migdaliusLaravel
2

Query not ordering by start_date?

Hi there, I'm new to the Laracasts forum and I look forward to using this forum to learn more about Laravel. I've created a query below to output events for a user. It all works fine, except to the orderBy doesn't actually order the events by start_date. Can anyone assist with this query? I've used the raw SQL for this query and it seems to work, so I'm not sure why it won't or

thomas93's avatar
thomas93's avatar thomas93Laravel
3

Query doesn't get executed when using orderByRaw()

I'm rather new to Laravel (ex frontend dev here) and today I've run into my first issue that I cannot figure out. So I'm running a query on my User model and I am trying to use a raw statement for ordering : $query = User::query() ->whereNotIn('username', $blockedUsers) ->orderByRaw('FLOOR(((comment_count - discussion_count) * 10 + discussion_count * 21 + votes *

iPurpl3x's avatar
iPurpl3x's avatar iPurpl3xEloquent
3

Help with eloquent query with a calculated field

Hey guys, I got a model Event which has a Report and this report has many Workers. Well, im trying to get all workers with reports in a date ($year + $month) with the time than they spend in this reports, this is calculated field: public function getMinutesAttribute() { $start = Carbon::parse($this->start_at); $end = Carbon::parse($this->end_at);

dani94's avatar
dani94's avatar SnapeyEloquent
1

how to sort outer query based on a internal query field??

hello I need to sort all products based on their average rating here is my query: category->products()->whereHas('reviews',function($query){ $query->groupBy('reviews.productId')->select(DB::raw("avg(rating) as product_rating")); })->orderBy('product_rating','desc'); the error says that product_rating field doest exists. how can I rewrite this to be ab

danimohamadnejad's avatar
danimohamadnejad's avatar SnapeyEloquent
5

how to get multi lists together using one query?

hello I am working on ecommerce website with laravel. I want to display most wished for, top rated and best seller products in category page. is it possible to grab them all using one query or should I use three different queries what is best practice? please explain thank you

danimohamadnejad's avatar
danimohamadnejad's avatar SnapeyEloquent
3

Eloquent Model query with custom select/join/order

I have models called Post, and PostView. A Post can have many PostViews. I need to get a paginated list of Posts ordered by the count of PostViews created within the last 30 days. I can do this using withCount: // works but S L O W $posts = Post::withCount(['views' => function($query) { $query->where('post_views.created_at', '>=', 'DATE_ADD(CURDATE(), INTE

mbeckett's avatar
mbeckett's avatar bobbybouwm...Eloquent
5

How to Query GroupBy

I have users,quizzes and quiz_users table. users table have id/name/email/password/timestamps quizzes table have id/name/description/timestamps quiz_users table have id/user_id/quiz_id so i have 2 model User.php and Quiz.php and quiz_users is pivot table, it contains the particular quiz assigned to particular user, user_id and quiz_id Now i want to fetch all users groupBy quiz_

david001's avatar
david001's avatar emadigaLaravel
3

how to sort a query base on 2 different conditions in laravel

i have a query in laravel that i want my user to be able to sort it in 2 different ways . one of them is hits which is the model it self field but the other is the field which is in relation of that model that i am loading in my resource : $data = Accommodation::with(['city','accommodationFacilities','gallery', 'accommodationRoomsLimited.roomPricingHistorySearch' => funct

farshadf's avatar
farshadf's avatar farshadfLaravel
0

Refactoring DB Select sub query

What is the best way to call the below mentioned DB:Select query. I don't want to use eloquent. How can I bind parameter in the subquery. DB::select('SELECT * FROM ( SELECT A.id as id,A.name,C.category_name FROM alerts AS A LEFT JOIN categories as C ON A.category_id = C.id where A.id < '. $OldLowestID.' order by id asc

ssquare's avatar
ssquare's avatar bobbybouwm...Laravel
1

how to use 'with()' in Laravel DB Query Builder ?

In this case I gave up to use ORM Eloquent . SO I decided to use DB Query builder only. But I want to drag 'user' model with my 'results' model . How can I get success ? $bettings = DB::table('results') ->select('1st', '2nd', DB::raw('SUM(time_laps) as time_laps')) ->where('event_id', $event_id) ->join('type', function( $join) us

binggle's avatar
binggle's avatar binggleEloquent
5

Form a query with custom column with subquery as it's value

Hello, i have query that joins few tables. Result of query returns me the following column values "prod_id" : product.id "prod_name" : product.name "prod_user_id" : product_user.id "prod_user_prod_id" : product_user.product_id "prod_user_user_id" : product_user.user_id ... now i wanna do a second query (maybe there are other op

Aktheon's avatar
Aktheon's avatar jlrdwEloquent
3

how to chain relationships with query builder

i'm trying to get editor and poster data for every article in single query, unfortunately i'm not getting these data on the final query. this is my code public function widgets($id = null) { if($id){ $pageID = $id; }else{ $id = 1; } $widgets = Widget::with( ['category.arti

nhayder's avatar
nhayder's avatar Sti3basEloquent
4

Where retrun null if using wherehas after in laravel query

Where retrun null if using wherehas after in laravel query This ->where('name', 'LIKE', '%'.$schoolname.'%') return nothing. Why? public function listecoles(){ $schoolname = request()->query('query'); $location = request()->query('location'); $type = request()->query('type'); if($schoolname || $location || $type){ $schools = School::wit

Mawunyo's avatar
Mawunyo's avatar SnapeyLaravel
2

Using latest()->first() in a local query scope on relation gives an error

I have a Task model which has a M:M relationship setup called transfers. This links Task & User models through a pivot table called transfer_task. The following code works fine and displays just the tasks that have been transferred to the current user $current_user_id = \Auth::user()->id; $query->orWhereHas('transfers', function ($query) use($current_user_id) { $q

clinta's avatar
clinta's avatar ResinEloquent
5

Query which works in SQL Client but not in Laravel? SQL General error 2031

Hi. Following my previous post I finally found how to "convert" my SQL raw query into Laravel query builder to be more eye-friendly and maintainable: SELECT DISTINCT `name`, `phone`, `address_town`, `address_street`, `address_postal_code` FROM ( SELECT `postal_code` FROM `towns` ORDER BY ST_DISTANCE(`location`, ( SELECT `location` FROM `tow

RomainB's avatar
RomainB's avatar HijrahSubh...Eloquent
3

Laravel sum query using join and distinct

I have 2 tables to query using join namely, loans and amortizations table. Loans table has many amortizations, so loans.id is reference as amortizations.loan_id in amortizations table. Loan id:3 exist twice in amortizations table, each with a value=10,800. Loan id:5 also exist twice in amortizations table, each with a value=11,100 and the same goes with Loan id:11 that also exi

aac2018's avatar
aac2018's avatar atfinhoLaravel
4

convert query builder to eloquent

Is there a way to transform this query builder to eloquent ? $products = DB::select("SELECT *, (CASE WHEN datediff(expire_date,CURDATE()) > 0 then datediff(expire_date,CURDATE()) ELSE 'Expired' END) as remaining from products");

bekaskaki's avatar
bekaskaki's avatar bekaskakiEloquent
2

Optimize Relationship Query

Hey there, Any help on this would be greatly appreciated. I have 3 tables. inventory product inventory_product They all have their associated models and the inventory and product table are related through a belongsToMany relationship through the inventory_product table. On this pivot table, there is an additional pivot column for quantity. Thus, a product can exists with diff

futurefuture's avatar
futurefuture's avatar futurefutu...Laravel
2

Inefficient query how to impove number of queries

I am running into an issue where the query I have written is too slow and falls over when the request is asking for quite a lot of data. To explain further - there is a results table that holds all the user results. A user is belongs to an entity. The user also has associated compliance which is required to be attached to the user along with the results in the returned data. He

garethredfern's avatar
garethredfern's avatar Michael786Eloquent
20

Query strings dynamically updates inputs in form

Greetings! I'm very new with Vue.js, and i'm stuck with a problem that involves forms and dynamically updates of query strings. Let's assume that I have a server running in localhost:8000 My index.php have just 2 inputs: <input name="foo" v-model="foo"> <input name="bar" v-model="bar"> I want that every changes made in inputs

pedroroccon's avatar
pedroroccon's avatar pedrorocco...Vue
2

Multi level Query for search via Job Creation dates range and Port names

Need suggestions from experts for a feasible way to filter ports with filtering port->vessels->job->created_at public function port_reports() { $port = request('port'); $searchfrom = request('searchfrom'); $searchto = request('searchto'); ​ $ports = Port::WhereHas('vesselLoadings.job', function ($query) use ($searchfrom, $searchto)

imamhsn195's avatar
imamhsn195's avatar deepu07Eloquent
1

Problem with turning laravel search query in elasticsearch

I have search form in laravel project that uses search with laravel eloquent query. I am trying to build more advanced search using elasticsearch, but I am new to this and I need help to transform this query to elasticsearch query. Any help is appreciated. Here is my query. $query = Property::query(); if (Request::has('propertyType')) { Request::get('propertyType'); } $pr

Rakic's avatar
Rakic's avatar mabdullahs...Laravel
1

Segmentation fault on Eloquent query

Hi, I get a segmentation fault ("Segmentation fault: 11") when I run my Eloquent query. I don't know the cause and I'm wondering why I get this error? This is my query that triggers the error: $category_ids = [19,20,21]; $messages = Message::whereHas('categories', function($query) use ($category_ids) { $query->whereIn('categories.id', $category_ids); })->get();

Barley's avatar
Barley's avatar BarleyEloquent
3

empty paginate if query with addBinding

I have Eloquent query with joins and conditions. The problem is that with this line, my query works weird: $query->addSelect(DB::raw("IF(news.user_id= ? , 1, 0) as edit"))->addBinding($userId); $query->get(); // returns collection of 27 items $query->paginate(15); // returns empty collection $query->simplePaginate(15); // returns

tumas's avatar
tumas's avatar tumasEloquent
1

laravel advance where query builder

I have an advance where query and need to set operation for that like below but it's not working $query->where(function($q) use($filter, $field){ $this->applyFilter($q, $filter, $field); }, $filter->operation); the $filter->operation could be one of or | and how I could implement this know it just and the query

Farshad's avatar
Farshad's avatar FarshadEloquent
1

Single Eloquent Query

My task is done but problem with single query.So I want to count data with current date and future date and with status in single query . My Scopefunctions public function scopeAction($q){ return $q->wheredueDate(carbon::now()->toDateString())->whereTaskStatus(self::TASK_STATUS_INCOMPLETE); } public function scopeSnooze($q){ return $q->where('due_date','>

yusuf128's avatar
yusuf128's avatar yusuf128Laravel
2

Self join query is too slow

Below query is taking approx 7 to 8 seconds. I think self join in laravel is taking too much time when there is huge rows. any suggestion? table has 48000 recoreds. $excludeUser = [1,2,3]; $userIds = [5,6,8,9,8]; \DB::table('usr_contacts as uc1') ->select('uc1.contact_id as uid', 'uc2.contact_id as id') ->join('usr_contacts

prex016's avatar
prex016's avatar YaserSelimLaravel
9

How to run query with model function?

So, I have a model for products of a website. In the model I use a function to calculate the price of the product. It is something like this, class Product extends Model { public function price() { // logic to calculate price // this logic may depend on many things and may change in future. return $price } } I can view this successfully in

nabilunfarhanun's avatar
nabilunfarhanun's avatar mabdullahs...Eloquent
1

SQL Query with Variable / Laravel 5.6 MacOS

Hi all, Im trying to amend my SQL Query which is working fine to use an variable (or a helper function). $aa = DB::select('select projects.*, users.*, thumbnails.* from thumbnails inner join projects, users where projects.id = thumbnails.id && users.id_user = projects.user_id'); I would like to use this instead of users.id_user = projects.user_id $this_user = auth::u

zerodps's avatar
zerodps's avatar zerodpsLaravel
2

Query Arabic text using DB with PDO

Hi, I'm trying to query some Arabic records using this code: $pdo = DB::getPdo(); $statement = $pdo->prepare('select * from workers'); $statement->setFetchMode(\PDO::FETCH_ASSOC); $statement->execute(); $results = $statement->fetchAll(); return view('payslips')->with('data', $results); The Arabic text return as "" and can't see the values when I dd($r

Napster's avatar
Napster's avatar NapsterLaravel
2

Want to convert sql query to laravel

I have this sql query which is working fine. I wanted to convert this to laravel,which i am not able to make it working. plz help. SELECT id, ( 6371* acos ( cos ( radians(12.9166) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians(77.6101) ) + sin ( radians(12.9166) ) * sin( radians( lat ) ) ) ) AS distance FROM doctors HAVING di

gaan10's avatar
gaan10's avatar SnapeyLaravel
6

retrieve data related to related table on model query

hello, i have got tables articles, files and uploads my current query ArticleNew::where('article_id', 2931) ->with([ 'files' => function($query){ $query->select('url', 'filename', 'mimetype', 'db_files_new.file_id') ->orderBy('file_id'); }, 'uploads' => functio

Rymercyble's avatar
Rymercyble's avatar RymercybleEloquent
0

Help with Query involving two tables

Hello, I am still very much a beginner. I am looking for help with some code I'm trying modify old code involving a query. Here's a rough breakdown of the situation: There are two tables, HOUSEHOLD and INFO HOUSEHOLD (contains 'id', 'inhabitant_id', 'house_id'). some households have 'house_id' as NULL if they have never been painted 'inhabitant_id' is NULL if vacant INFO (c

hikerquinn's avatar
hikerquinn's avatar jlrdwEloquent
1

Eloquent Relationships vs Where:: query

So basically to query my database tables I got used to using the where:: method instead of defining relationships in the model and using those methods to query the database. Would this be a matter of preference or are there advantages to using relationships instead of the where:: method? Thanks!

lukegalea16's avatar
lukegalea16's avatar lukegalea1...Laravel
5

Laravel query builder returning more rows than intended

Have a table structure like: properties ------------ id property_name buildings ------------- id building_name property_id floors ------------- id floor_coode building_id units ------------- id unit_number floor_id amenities ------------ id amenity_name amenity_value unit_id categories ----------- id category_name parent_id amenity_categories

ssquare's avatar
ssquare's avatar SnapeyLaravel
3

Raw query generating General error 2014

One of my queries is generating following error : General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Here is the query : $query2 = DB::select(DB::raw(" SELECT date,token,(select date from token b where gate='IN' and type='BIKE' and date(date) between :fd and :td and b.token=a.token) as intime

Zaheer's avatar
Zaheer's avatar ZaheerLaravel
2

pass a mysql query to a laravel query builder

I have a slightly complex query that I first made in flat mysql, but I don't know how to translate a part of it in the query builder of Laravel, the part I would like to pass is this: FROM ( SELECT 1 AS month UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION

MonoDev's avatar
MonoDev's avatar jlrdwGeneral
3

Model::create VS Model:insert (Number of query)

When to use insert method instead of create ? in this example the insert perform only one query for 3 tags : $inputField = 'tag1,tag2,tag3'; $tags = explode(',', $inputField); $data = collect($tags)->map(function ($tag){ return ['name' => $tag]; })->toArray(); Tag::insert($data); // 1 query create 3 query $inputField = 'tag1,tag2,tag3'; $tags = explode(',

untymage's avatar
untymage's avatar tykusLaravel
1

Use chunk to get query still slow

Hi guys, I building a system that need to check a records, it's output around 5,000 rows. When i use dd($data) it trace out very fast in 200ms, but when goes to view it took around 10seconds to finish loaded. I try to use chunk to get query, it had improve loaded out speed but still slow. I try to load only {{ $row-id }} per row it become more fast, it there any problem with my

kankai's avatar
kankai's avatar SnapeyLaravel
3

Custom Query To eloquent

Hello, i am new in laravel, and i don't know how to convert a custom query to eloquent i have a payment cards system, and want to display a summary about activated and deactivated cards, with prices and counts my query is : SELECT *, activatedCardsPrice + deactivatedCardsPrice as totalPrice FROM ( SELECT *, SUM(cardValue * deactivatedCardsCount)

engHusseinMansour's avatar
engHusseinMansour's avatar jlrdwEloquent
4