Writing better query that has whereRaw

I wrote a query like this. I think whereRaw part could have been written far better and eventually be more performant but I couldn't succeed. DB::table('messages') ->select('*') ->where('message_type', '=', $message_type) ->whereIn('seller_id', $seller_id->pluck('seller_id')) ->whereNull('answer_id') -&g

SNaRe's avatar
SNaRe's avatar jlrdwLaravel
1

return similar values in two arrays in Eloquent query

Hi all, I have user_data table contain column sub_sectors and its value string like (2,3,4) And I have another table called (orders) also contain column called sub_sectors and its value as json like: [{"subsector":"2"},{"subsector":"3"},{"subsector":"4"},{"subsector":"5"},{"subsector":&quo

greatsami's avatar
greatsami's avatar rumm.anEloquent
1

laravel closure query with limit

Hi all i have a closure query like below. $motors = Motor::with(['advertisementPhotos', 'customer.dealer', 'adAttributes' => function ($query) { $query->with('attribute'); // $query->where('content', 'like', 'foo%'); //; $query->limit(2); }])->where('featured', 1)->where('status', 1) ->where('date_expiry', '>=', date('Y-m-d') . ' 00:00:00')

amjadkhan896's avatar
amjadkhan896's avatar amjadkhan8...Eloquent
0

Is there any way to optimize this query?

Can this query be optimized? It seems to be too complicated, Laravel always seems to have a more elegant answer $category = $category->load(['products' => function ($query) { $query->with(['features' => function ($x) { $x->with('field') ->whereHas('field', function($y) { $y->whe

JackJones's avatar
JackJones's avatar tisuchiEloquent
1

How to convert Query Builder to Eloquent?

Hi, I have been searching for a list of all eloquent queries but no luck. So Can I just convert Query Builder Queries into Eloquent? For example. Query Builder : DB::table('users')->where('name', 'John')->value('email') Eloquent : Users::where('name', 'John')->value('email') Will it work for every DB Query? Thanks for your answers.

yidekoh556's avatar
yidekoh556's avatar lostdreame...Eloquent
3

Raw query count problem with where condition...

I am not able to add where condition in count raw query... this is my actual code and i want to trying add where condition in following query : $rawQuery = DB::raw("count(table_2.fk_id) as countTotal"); $sessionHistory = table_1::select('table_1.id') ->leftJoin('table_2', 'table_1.id', '=', 'table_2.fk_id') ->selectRaw($rawQuery) ->groupBy('table_1.id') ->

heyIm's avatar
heyIm's avatar jlrdwLaravel
1

Query to group by alphabet & also get total count and group count

I have a table for categories. I want to group each category by alphabet. I want to get count of each group as well as total number of categories. Would be nice if I can achieve this in one query. Thanks

viraj's avatar
viraj's avatar virajLaravel
1

Relation query with dynamic parent condition

I have two tables. catalogs - id | name photos - id | photoable_id | photoable_type When I need all catalogs with photos, is simple, all works: $catalogs = Catalog::with('photos'); But every Catalog can have main photo, in catalog table I have too aditional column main_photo_id (id into photos table or can be nullable...). How can I make query for Catalogs with photos, but wi

inyansuta's avatar
inyansuta's avatar ekhlasEloquent
3

Save query on db listen event

I have a requirement that I need to save each and every query executed by the laravel program. I have created following listener in boot method of app/Http/Providers/AppServiceProvider public function boot() { DB::listen( function ($query) { $query_binding = ''; foreach ($query->bindings as $binding) {

JanakaDombawela's avatar
JanakaDombawela's avatar adars47Laravel
1

Return CSV from an API route that uses Spatie Query Builder package

I'm looking for some initial guidance/help, basically ideas on how to approach my task. I'm using the Spatie Laravel Query Builder package for my apps API. So, I can filter Models etc. (https://github.com/spatie/laravel-query-builder) Returning JSON is great, but I'd also like to return CSV files. What recommendations do people have for grabbing the Query result and converting

luke___'s avatar
luke___'s avatar luke___Eloquent
0

Question regarding concatenating assignment operator in Eloquent Query builder

I'm working on SQL queries using Eloquent, and I'm having trouble concatenating my SQL strings in my query. $search = DB::table('players') ->join('nfl_team', 'nfl_team.team_id', '=', 'players.team_id') ->join('colleges', 'colleges.college_id', '=', 'players.college_id') ->join('player_positions', 'nfl_team.team_id', '=', 'players.team_id') if (i

vinnliang's avatar
vinnliang's avatar vinnliangEloquent
4

Mark all fields from a query

I have a column in a database where the I want to search user_id and mark all fields when I get the result. I am not sure how to mark all the fields when I have to data. my query : $user = PrivateMessage::where('recipient_id', '=', $request->authedUser->id)->get(); then I want to set PrivateMessage->read = 1 how would I do this

richardh's avatar
richardh's avatar CinekEloquent
1

What is the purpose of the `all` key in an eloquent get query

Say I have the following query Bed::where('user_id', 1)->get() all: [ App\Bed {#796 id: 1, user_id: 1, name: "sgdffd", gender: "male", type: "single", status: "available", created_at: "2018-03-19 07:53:51", updated_at: "2018-03-19 07:53:5

davidyu's avatar
davidyu's avatar bobbybouwm...Eloquent
4

laravel where query on attributes

My database schema: id|user_id|choosen_number|win_number 1|2|5|7 2|2|7|7 My query: Withdrawal::where('user_id', 2)->where('choosen_number', 'win_number')->get() for some reason the returned collection is empty all the time. Any idea?

t0berius's avatar
t0berius's avatar SnapeyEloquent
3

Caching a query result

Hi, What is the best way to cache a query result if the page have a filter option? Do I get all the result and cache it then just manipulate the result from there? Thank you in advance guys.

oroalej's avatar
oroalej's avatar SanctuaryEloquent
1

Query - Left Join - Expire Post after 30 days

Pretty new to Laravel and I'm trying to update the code of an old project to Laravel. My question is how should I go about writing a LEFT JOIN query? In this example I will use a classifieds page. I understand how to show my classified listings from the database. I would write my classified controller like so: public function index() { $classifieds=Classified::a

daugaard47's avatar
daugaard47's avatar daugaard47Laravel
2

exponential value to numeric in controller query

i have a table with column rate which is in exponential,needs to convert it in numeric. this is my sql query that runs fine in database SELECT CAST(1.6e+06 AS rate)

devamit2018's avatar
devamit2018's avatar bobbybouwm...Laravel
1

Check when a query was successful

How would I check this query was successfully executed: DB::table('private_messages')->whereIn('id', $remove_ids[0])->delete();

richardh's avatar
richardh's avatar tykusLaravel
3

Duplicate results in endless pagination when new entries added to ajax query

On my website, users can post images. When a user browses images, if he scrolls down to the bottom of the page, new images (7 in total) will be fetched. JS: $(window).scroll(fetchImages); function fetchImages() { var page = $('.endless-pagination').data('next-page'); if(page !== null) { clearTimeout( $.data( this, "scrollCheck" ) ); $.data

Azoruk's avatar
Azoruk's avatar biishmarJavaScript
2

Make one query to go through all tables

Is there a way I could connect this two queries? I need to get category under every meal. public function scopeMyMeals($query, $language){ $mealPivot = $this->meals()->getTable(); $categoryPivot = $this->categories()->getTable(); $query = $query->whereHas('meals', function($q) use ($language, $mealPivot) { $q->where('code',

AlenV's avatar
AlenV's avatar wilk_randa...Eloquent
1

Convert sql query to laravel query builder

This is the sql query I have four tables answers, questions, users ans upvote_answers. I get the result by joining three tables answers, questions and users . But i also wanna know for logged in user where he has upvoted an answer or not. SELECT answers.answer_content as answer_content, answers.created_at as answer_upvote, answers.created_at as answer_downvote, questions.questi

waqaar541's avatar
waqaar541's avatar santhusury...Laravel
4

Select Game With Screenshot Relationship But Only Where Screenshots Exist. Query Not Working

I'm trying to select Games with Screenshot relationship, but only where at least one Screenshot image exists. For some reason it also includes Games without existing screenshots and I am really unsure why that happens. End-result expected: Get 3 games, with one screenshot in size "t_screenshot_huge" where hypes are greater than 1, order results by random so always get

madsem's avatar
madsem's avatar BezhanSall...Eloquent
5

Eloquent Query Issue

Hi all, I have one query issue. I have small table. having four columns that are customer_id, job_id and timestamp. I already created one to one relationship. for both tables now the issue is I want to to show the total applicants applied for the same job. My Query at the moment is CustomerJobApplication::with(['jobApplied' , 'customer')->where('customer_id',auth()->guar

amjadkhan896's avatar
amjadkhan896's avatar amjadkhan8...Eloquent
3

Binding params with Raw Query

I have a query for a paginated listing page. Some part of the SELECT and WHERE parts are raw queries. To avoid sql vularabilities, I need to use parameter binding for this query. It is worked fine for WhereRawquery but not worked in SELECTsection. Please see my query here: $aJob = JobKeywords::where('job_keywords.status', 1) ->where('job_keywords

harit's avatar
harit's avatar haritLaravel
2

Sort eloquent query by model relationship

So I have the following query $post = Post::where('id', $post_id)->with('ratings', 'ratings.user', 'ratings.like_votes')->firstOrFail(); How do I sort ratings by ratings with the most like_votes?

jpeterson579's avatar
jpeterson579's avatar jpeterson5...Eloquent
2

How to query from a related model with foreign key id.

I have 2 tables : Users and Couriers > users id | email | password | username | You know what I mean > couriers id | user_id(FK) | status | no_of_parcels I have to get the currently logged_on user which has an Id of 3 in the User model with that. if I have a user_id of 3 in the couriers table which has the id of 280 Then In My query : $id = something like_ auth(

abduljakul-salsalani's avatar
abduljakul-salsalani's avatar crnkovicLaravel
1

writting distinct query example

i need show the unique values for whole table i have searched in google the query written was DB::table('products')->select('color')->distinct()->get(); the above query displays the unique color value how to query for whole table (correct me if i am wrong) DB::table(products')->select('*')->distinct()->get();

Shawdow's avatar
Shawdow's avatar ShawdowLaravel
10

Multiple select clauses not working properly in DB query

Hi, I'm running a query across 2 tables with unexpected results. When I use the following lines of code, the result ignores the clause "where users.userType = Student" $searchword = Input::get ('q'); $student = \DB::table('userprofiles')->join('users', 'users.userName', '=', 'userprofiles.userName')->select('userprofiles.id','userprofiles.userName', 'userprofile

jayrec's avatar
jayrec's avatar jayrecLaravel
6

My query using nested eager loading dose not trigger the targeted data!

Hello all, I want to query some data using nested eager loading , but it appears my query only fetch the first relation! Here are my tables: users: id posts: post_id,user_id commetns: comment_id, post_id,user_id the models: User: public function posts() { return $this->hasMany('App\Post'); } public function comments() { return $this->hasMany('App\Comment'); } Post: pub

voilatech's avatar
voilatech's avatar voilatechEloquent
14

'Method whereBetween does not exist' when using cached query.

Still relatively new to Laravel (5.5) so I think I'm not understanding this correctly. I'm caching a database query in Redis using the following: $log = Cache::remember('log'.auth()->id(), 10, function () { return Log::select(['id', 'clientip', 'token', 'timestamp'])->get(); }); I want to perform a whereBetween on the cached result and have tr

ElijahPaul's avatar
ElijahPaul's avatar ElijahPaulEloquent
6

Remove URL query Parameters form url

I want to get the slug of a product but as part of that I am performing a query to get some data related to the product form another table. This is how am displaying my product in my .blade view; <form action="{{ route('user.showProduct', $productProductOption->product->slug) }}" method="GET"> <input type="hidden" value="{{

nanadjei2's avatar
nanadjei2's avatar BorisuLaravel
1

SQL query to Eloquent

I have the following SQL query that does exactly what I want: select * from locations where (select count(*) from machines where game_id = 1 and locations.id = machines.location_id) > 0 and (select count(*) from machines where game_id = 2 and locations.id = machines.location_id) > 0 However the amount of game_ids can be from

MarioMacedo's avatar
MarioMacedo's avatar MarioMaced...Eloquent
4

Laravel 5.5 WhereIn query with null

return $places = Place::where('name', 'like', '%' . $request->name . '%') ->whereHas('eatCategories', function($q) use($kitchen){ $q->WhereIn('eat_category_id', $kitchen); }) ->whereHas('services', function($q) use($service){ $q->WhereIn('service_id', $service); }) ->whereHas('paymentMethods', function($q) use($payment){ $q->WhereIn('place_attribute_id', $p

mrtsglk's avatar
mrtsglk's avatar lostdreame...Eloquent
1

How to query json column array.

I tried doing this // Migration $table->json('map_data')->nullable(); // Map data is map_data = { 'bands' => ['1', '2'], } // Query $query->whereIn('map_data->bands', ['1']); I'm getting this error QLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version fo

jericopulvera's avatar
jericopulvera's avatar Tray2Eloquent
10

Pagination of GROUPED query results within Laravel 4.2

I have a query which currently returns in the region of 25k records, with this likely to exceed 100k by the end of year. Obviously this is paginated for readability..... Having delved into the code for pagination, it appears that the standard Laravel 4.2 code returns the entire dataset in order to get the TOTAL count, and then slices the resulting collection in order to return

absolute's avatar
absolute's avatar absoluteLaravel
0

Get Subject name from parent query for use in subquery.

I have to get the subject name of every row for the query newsubject = " ". the query required 4 parameters and 3 have been provided by an input. The subject is to be obtained from the view row. i want to be able to obtained the rank to subject with a class of student with certain academic year and programme. how do i get $subject from the same parent query. $getProgr

kosky2005's avatar
kosky2005's avatar CronixLaravel
4

Should I use Model Relationships vs Query Scopes and a simple mysql join?

Perhaps I am missing something; but when using .. belongsTo('App\Org') or $members = return $this->hasMany('App\Member'); models, I am finding that I often just return more than I need. Ie: If an Org has Members; $org->with('members'); gives me Org Members If i want user data I have to do $org->with('members','members.user'); Which gives me Org Members User Record

inmn's avatar
inmn's avatar BorisuEloquent
5

Validate the query string

Is it possible to validate the query string? We have a number of GET routes used for components, mostly searching and filtering, and we'd like to validate the provided input. For example, that the currency ISO code exists in the database. example.com/some/route/that/returns/data?currency=GBP

mstnorris's avatar
mstnorris's avatar sutherlandGeneral
1

Optimizing eloquent query

I have a page that checks whether if a product is liked by the logged in user and also the likes count. I have read that if I write $this->likes->count() the process is slow the app first will create a collection with all the likes then it will count them. but I need two things. If I change the likes to likes() in my queries, it will do two requests to the db while if I h

AR's avatar
AR's avatar bobbybouwm...Eloquent
1

query builder fails on scope: strtolower() expects parameter 1 to be string, object given

I was able to compile this query builder and run it on tinker like so: \DB::table('stores')->selectRaw('stores.ref,t.text as ref_ar')->join(\DB::raw("(select item, text from translator_translations where locale ='ar' AND namespace ='*' AND item like 'store.ref%') as t"), function($join) { $join->on(\DB::raw('SUBSTRING(stores.ref_translation FROM 14 FOR 26)'),

abbood's avatar
abbood's avatar bobbybouwm...Laravel
1

Query whereBetween with date

Hi, I'm playing with the whereBetween - query for 2 dates. Given is a date 2018-10-04 01:02:03 So the whereBetween should ignore the hours and seconds, is that possible? $query->whereBetween('date', [$startDate, $endDate]); with $startDate = 2018-01-01; and $endDate = 2018-10-05; works fine, if I set day in the $endDate to 04. it's not returning anything (for sure not). Than

matz's avatar
matz's avatar matzLaravel
6

Raw query with strftime

Hi, I have a hard time compiling the DB:: code for a query that involves a raw where statement. The raw query looks like this: select * from texts where strftime('%Y-%m', updated_at) = 2018-02; So far, I've come up with the knowledge that using this strftime(...) section in the following segment works perfectly fine: DB::table('texts')->select(DB::raw("strftime('%Y-%m'

ITellMyselfSecrets's avatar
ITellMyselfSecrets's avatar bobbybouwm...Laravel
1

Make custom query builder method (query scope) for all models in Laravel 5.5

I have multiple models, all with timestamps. I'm often using whereDate to get all rows from today and yesterday, like this: ModelName::whereDate('created_at', now()->today())->get(); ModelName::whereDate('created_at', now()->yesterday())->get(); I want to have it shorter, simplier, like: ModelName::today()->get(); ModelName::yesterday()->get(); I can not fin

verona's avatar
verona's avatar SairahcazEloquent
4

How would you get the Eloquent Model? Using a Raw Database Query for GeoLocation

I've no clue how to transform this query to get an eloquent model. return DB::select('SELECT *, ? * 2 * ASIN(SQRT(POWER(SIN((? - abs(dest.lat)) * pi()/180 / 2),2) + COS(? * pi()/180 ) * COS(abs(dest.lat) * pi()/180) * POWER(SIN((? - dest.lng) * pi()/180 / 2), 2) )) as distance FROM sell_maps dest having distance <

shadrix's avatar
shadrix's avatar d3moEloquent
2

DataBase Query : return all results if field is null

With my app I have search function including 3 fields (cargov, carname, carmodel). I want my user to be able to select the fields that he wants for search so if the car name filed is not provided by the user ( null, same as for the car model filed ) the query should return all results, I tried the following and it's working only when providing all the fields but it doesn't work

mriyadh's avatar
mriyadh's avatar shez1983Lumen
13

Query Builder "addGroupBy()" function doesnt works.

Hi, I'm trying to use 2 groupBy in a query, found this "addGrpupBy" in the QueryBuilder.php file, so i thought it might work, but it doesn't. Here is the query. $sessions = Session::select(array('sessions.display_name', 'sites.name', 'programs.name')) ->join('programs', 'programs.session_id', '=', 'sessions.id') ->join('sites', 'programs.site_id', '=', 'sites.id

Vicente123's avatar
Vicente123's avatar Vicente123Laravel
4

Inner Join nested in Outer join using query builder

I am using Laravel 5.5 and want to do a slightly more complicated query. I want to get all the Nodes no matter if they have an Image. Further I don't want to return any records from Images_Nodes if there is no corresponding record in Images. This is a simplified version of a more complex query. select n.Node_id, i.Image_id from Nodes AS n left join Nodes_Images AS

tylerclendenin's avatar
tylerclendenin's avatar jlrdwLaravel
2

OrderByRaw is removing results in union query

I am running this query in a project where I am having "meta_competitions" and "primary_events" that is going to merge together into a result where they do not overlap by primary_event_meta_competitions having the primary_event_id of the current primary_event. Everything is working as exprected in the query itself, we are getting the results we want. But whe

jimmitjoo's avatar
jimmitjoo's avatar jimmitjooLaravel
1

query display particular subcategory for a particular category

Hi, below is the table which has id,name,parent_id. the parent_id is the foreign key which refers to id column (categories and subcategories) id name parent_id 1 Elect NULL 2 Cam NULL 3 Appliances

Shawdow's avatar
Shawdow's avatar ShawdowLaravel
8

No matches from fulltext search index query within laravel test suite

So I have a very simple search method on a "Note" model that looks like this: public static function search($query) { $results = self::whereRaw("Match(name,body) AGAINST('$query')") ->get(); return self::processSearchResults($results, $query); } The processSearchResults() just attaches some extra fields onto the results - nothing to wo

joeyrush's avatar
joeyrush's avatar WillFTesting
16