Turn off only_full_group_by for just one query (Guide)

I see many questions come up with the error: SELECT list is not in GROUP BY clause and contains nonaggregated column ....... Many answers are telling the poster to change strict mode from true to false in the database config file. But you can just do this for one query only by adding before the query: config()->set('database.connections.your_connection.strict', false); //

jlrdw's avatar
jlrdw's avatar bugsyshaGuides
1

DB query which has exactly two relations

I build a message system. When a user creates a new message to a user i will check if there is already a conversation for theses users to attach the message. Otherwise a new thread will be opened. I have problems to create the query in laravel. return $this->whereHas('participants', function ($query) { $query->where('user_id','=',1)->where('user_id','=',57); })->get

Pixelairport's avatar
Pixelairport's avatar SinnbeckEloquent
5

Reusing eloquent builder parameters inside query builder scope

I have a scope which picks out the first result per group for a set of results. public function scopeFirstPerGroup(Builder $query, ?array $fields = null, string $by = 'id'): Builder { return $query->whereIn('id', function (QueryBuilder $queryBuilder) use ($fields, $by, $query) { return $queryBuilder->from(static::getTable()) ->selectRaw("mi

booni3's avatar
booni3's avatar ahmeddabakEloquent
7

How to arrange mysql query result according to where conditions ?

I am running query like $query = "SELECT * FROM students WHERE name = ahmed or age > 20"; the result will be any person has name ahmed or greater than 20 years I want the name result get first ( all students have this name 'ahmed' ) not the age . How can i do this ?

EbrahemSamer's avatar
EbrahemSamer's avatar tykusGeneral
1

Laravel Eloquent filter record on with query

I have a relationship: User - Task (BelongsToMany). Tasks table: Task_user table: So, task #11 is assigned to both user #1 and #2 Then I create a table to log all the processes when a user performs a task. It is called: appraisals. Appraisals table: When the task is done, the percent field is set to 100 Now, I want to get unfinished tasks of each user. Unfinished task means

kakallatt's avatar
kakallatt's avatar kakallattEloquent
2

Append session variable to query string

Is there a way to append a query variable to each route? I need to addd a query parameter to each route if a flag is set in the users session. So if the user has like USD flag on their session I would like for each route to have a query string like currency=USD That way my vuejs app can get that variable and append it to the api calls that the vuejs components make.

jmurphy1267's avatar
jmurphy1267's avatar wilk_randa...Laravel
10

Query one-to-many relationship

Hey everyone, I'm trying to write a rather simple query. My voucher table has a number of vouchers, and each voucher is connected to multiple logs in the log table. I can easily get the vouchers that have a certain log status (say [10]) like this: $status = $query['status']; $source = $query['source']; $results = Voucher::whereHas('logs', function ($q) use($status) { $q->w

frittate's avatar
frittate's avatar ahmeddabakEloquent
1

Assign Raw query to Model

I want to make a custom query with DB class then i want to get all the benefits of the model, it's possible? public function fc() { $latestQueries = DB::table('queries') ->select('user_id', DB::raw('MAX(id) as last_query_id')) ->groupBy('user_id'); $users= DB::table('users') -

exSnake's avatar
exSnake's avatar jlrdwLaravel
2

query filter

d/m/y . ... and 02/01/20 -03/01/20 room 101 booked. 14/01/20-21/01/20 101 and 201 booked. my question is on 03/01/20 - 14/01/20 both 101 and 201 available, but in my query getting 101, 101, 201, is there is other way ??? $roomNotReserve =$room->bookings() ->whereNotIn('book_status_id',$statusCollect) ->where(function($q2) use ($ch

Aronaman's avatar
Aronaman's avatar a4ashrafLaravel
3

Laravel Auth still executing out-of-box insert query

Hello, Last night I ran installed php artisan ui vue --auth and have started to get everything integrated into my views. The problem that I'm having is that even though I have modified my migration and successfully added columns to the database and I've modified the RegisterController methods for validator and create with the updated columns, the query that is being executed is

Bacchus's avatar
Bacchus's avatar BacchusCode Review
2

Laravel generate excel from query returns an error using maatwebsite

Basically i got a query and the results will be created as an excel file. Here is my code $data = \DB::table('checkers') ->where('remarks_id',2) ->join('schedules','schedules.id','=','checkers.schedule_id') ->join('teachers','schedules.teacher_id','=','teachers.id') ->join('subject_codes','subject_codes.id','=','schedules.subject_code

vincentsanity's avatar
vincentsanity's avatar NakovGeneral
3

Generate excel based on the query from database using vue and laravel maatwebsite package

Basically I got a query to get all the record based on the date from and to. I use maatwebsite package in laravel. The problem is if i click the generate button it will not generate an excel file. Im using version 2.x of this package because it is the stable one. Can someone help me with this? I just only want to print the results of the query in the excel format. I wont change

vincentsanity's avatar
vincentsanity's avatar The-skepti...General
6

Custom Eloquent query scope with pivot

I had a look at Eloquent query scopes, but could not find a way to do the following: User model: class User extends Model { /** * Returns the items in the user's library. * * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany */ function library() { return $this->belongsToMany(Item::class, UserItem::class, 'user_id', 'item_id')

musa11971's avatar
musa11971's avatar ismaileEloquent
5

Laravel query issue

I need your help. I try to do this query: public function future_trips($cat_id, $city_id) { $trips=Events::where('start_date','>',Carbon::now()->subDay(5)) ->where(function ($q) { $q->where('category_id',$cat_id)->orWhere('city', $city_id); }) ->get();

serhiiposternak's avatar
serhiiposternak's avatar serhiipost...Laravel
2

Question about Transactions in DB query

Hello, I would like to optimize my query but I am not sure where to start. I have this: DB::transaction(static function () { $array = DB::table('orders') // First step look for expired orders ->with('user') ->where('created_at', '<=', now()->subMonth()->toDateTimeString()) ->where('notifications', '=', 0) ->toArray();

LaraBABA's avatar
LaraBABA's avatar BoubouEloquent
5

Group single Eloquent query by date

I have a table with records that have 'runs_at' dates. In the dashboard of the user I'd like to distinguish between records that are upcoming (date >= now) or records that are in the past (date < now). Ideally though I'd to get the results back in a single array so I can loop through them (nested) in the front-end. [ 'upcoming' => [ 0 => 'Record 1',

axtg's avatar
axtg's avatar axtgEloquent
2

How to convert this query to Eloquent ORM?

I have this query but I'm not sure how I would go about converting it into an eloquent ORM so I don't have to use raw sq. select date(o.created_at) as date_of_month, sum(order_sum) as total_order_sum, sum(i.total_energy_used) as total_energy_used, max(o.created_at) as lastorderdate, max(s.last_order_sum) as last_order_sum from orders o join ( select

martinszeltins's avatar
martinszeltins's avatar jlrdwEloquent
1

Running database query with user login request data

I want to run a database query with the user login form request data before logging the user in. I have been able to achieve this on my localhost by adding the query to AuthenticateUsers.php in my vendor folder but once I move to my cloud hosting service, the code to i added to my vendor folder in development is not included. I know it is not advisable to make changes to the fi

mspace's avatar
mspace's avatar thedesignl...Laravel
6

Correctly Query a Pivot Table

Hi, I have a conversation_user pivot table that whenever a user messages another user it creates 2 new rows. conversation_id = 1, user_id = 1,created_at = conversation_id = 1, user_id = 2,created_at = What is the correct way to write my query so I can check if a conversation exists between these two users and then get that conversation_id that corresponds to the two users? I

chrisgrim's avatar
chrisgrim's avatar chrisgrimLaravel
11

Error with Eloquent query builder 5.7

When i make query at this type: $record = Product::selectRaw('id < ?', [552]) ->where('id', 0) ->select('id'); query builder make this query: select `id` from `products` where `id` = '552' not: select `id` from `products` where `id` = '0' Used laravel 5.7

jooorooo's avatar
jooorooo's avatar joooroooEloquent
2

Why PDO insert Query executed two times ?

I am inserting using this code //////////////// $action_name = "بحث"; $action_doer_id = $_SESSION['user_id']; $query = "INSERT INTO transactions (transaction_name, transaction_user_id, transaction_notes) VALUES ('$action_name', $action_doer_id, '$notes')"; $stmt = $pdo->query($query); $stmt->execute(); ////////////// the result is two records added in

EbrahemSamer's avatar
EbrahemSamer's avatar programato...General
2

how to execute query to laravel code

query like this UPDATE items AS i INNER JOIN invoice_packages AS ip ON ip.id_invoice_package INNER JOIN invoice_details AS id ON ip.inv_detail_id = id.id_inv_detail INNER JOIN services AS s ON id.package_code = s.product_treatment_code INNER JOIN service_details AS sd ON s.id = sd.service_id SET i.begin_stock = (i.begin_stock - ip.used) WHERE i.product_id IN (i.product_id,ip

boill30's avatar
boill30's avatar boill30Laravel
2

Advanced query with Eloquent, using Scopes

I am using spatie/laravel-permission to manage roles and permissions. In the list of roles I want to make a personalized query based on the role of the current, authenticated user. Rule 1) If the user has the role of Super Admin, all roles are filtered without exception. Rule 2) If the user has the Administrator role, the roles are displayed, except the Super Admin. My models a

uicabpatweyler's avatar
uicabpatweyler's avatar bugsyshaEloquent
6

Laravel query is not working properly

I have a query to get all logged activity on a folder. The ID in this case is 2. I'm trying to get just records on 2, but as I have orwhere for search results, it gets all activity accross all folders for ths user. Any idea how to fix this query? $filtertext = $request->filter; $data = Activity::select('activity_log.*', 'users.first_name', 'users.last_name', 'users.email

JerodevS's avatar
JerodevS's avatar SinnbeckLaravel
6

Does this count as a example of n+1 query and can it be improved?

A little background, I have a table of debtors who pay back there loans periodically at different frequencies (daily, weekly, monthly). the code below is a going to be used by a cron job to update the next payment date for each loan depending on the payment frequency of each loan. I want to ask if this is an example of an N+1 query of some sort, and can I optimize this better p

seewhy's avatar
seewhy's avatar seewhyLaravel
3

Query builder not working as expected

$queryUnread = $queryRead = \App\UserActivity::select($select)->orderBy('users_activities.id', 'desc'); //All Unread notifications $notificationsRead = $queryRead->whereNull('activities_notifiers.read_at')->get(); //All Read notifications $notificationsUnread = $queryUnread->whereNotNull('activities_notifiers.rea

mihirpatel83's avatar
mihirpatel83's avatar tykusLaravel
4

getting different results in phpMyAdmin AND laravel with same query

I copy and paste the query from laravel to phpmyadmin and I get a different result, obviously it is the same database. why is this happening? I have this code: dump($usuario_id,$prueba_id ); $resultados = DB::select(DB::raw('SELECT smrt_preguntas.grupo_pregunta AS eval_grupo_id, SUM(smrt_respuestas.puntaje

dru's avatar
dru's avatar jlrdwLaravel
8

Is this an SQL injection bug or am I using param binding wrong in MATCH AGAINST raw query

DB::select('SELECT * FROM answers WHERE MATCH(search_terms) AGAINST( :term IN BOOLEAN MODE)', ['term' => $term]); It seems parameter is not escaped with this query (closing parenthesis breaks query). Is this an SQL injection bug or am I using parameter binding wrong in this raw SQL. Laravel: 5.8.36 MySQL: 8 PHP: 7.3 Parameter binding should offer protection against SQL inj

onujaar's avatar
onujaar's avatar onujaarLaravel
2

Laravel remove offset from the query

How can I remove the limit/offset from the below query? $query = TestModel::where('a', 'b')->limit(100); $query->removeLimit(); I'm using a query from another module and I don't want to change the code.

GirlioSalama95's avatar
GirlioSalama95's avatar GirlioSala...Laravel
4

Correct Query

I am doing clock in and clock out. In this regard I would like to insert clock out time of user where the clock in time is already inserted. My query is like below. DB::table('clocks') ->where('user_id', Auth::user()->id) ->latest('created_at ')->first() ->update(array('clock_out' => $request->clock_in)); Is it corre

afoysal's avatar
afoysal's avatar SnapeyLaravel
11

Blade syntax to query all of 3rd Level Eloquent Relationship

If I have set up all of my relationships correctly for Users->Contacts->Deals->Tasks, and I eager load all of a user's contacts into a view with their deals and tasks: public function index() { //Look up contacts associated with the logged in user $user_id = auth()->user()->id; $contacts = Contact::with(['contactnotes' => functi

WallyJ's avatar
WallyJ's avatar tykusLaravel
8

how to change or override the login query

i want to customize the login query used to authenticate the users. thanks

minaFaragAmin's avatar
minaFaragAmin's avatar minaFaragA...Laravel
2

Join with sub query in eloquent

I'm trying to get the running balance from a ledger. I am able to get the results I want from this query builder. But I need to pagainate the results and I don't see that paginate works on query builder. So I am trying to build the sql with eloquent, but I can't seem to figure out how to do it. Here is the current query builder. Thanks $ledgers = DB::select(DB::raw('SELECT tota

rszabo@crowleyauto.net's avatar
rszabo@crowleyauto.net's avatar jlrdwEloquent
3

Optimizing query

I've been running into this problem for a while now. I've dramatically reduced the loading time of this query from 30s to 3s but it's still taking to long in my opinion. This query is used in our chatbot, to get a response for a customer. It's all about this Eloquent query: /** * Only find sentences that are allowed to say back. * 1. WHERE NOT EXISTS

borrie's avatar
borrie's avatar SnapeyEloquent
3

No query results for model [App\Posts] Error

Hi guys, Hi, am facing an error like below when I trying to delete my post category. Can someone help me to resolve this? Error No query results for model [App\Posts] function public function delete(Request $request) { $posts = Posts::where('cat_id', $request->cat_id)->get(); if (count($posts) != 0){ foreach ($posts as $post){

sanjayacloud's avatar
sanjayacloud's avatar sanjayaclo...Laravel
7

How can I include a model function with this query?

I have a location model which has a custom function. Let's call it myCustomFunction for example. In my controller I am defining my query like this $locations = Location::with('lobs', 'dashboards', 'alerts') ->withCount('lobs') ->get(); However, I need to include myCustomFunction with the result set. I am not sure how to do this. Can anyone help me out

premiSoft's avatar
premiSoft's avatar zhanang19Laravel
8

Insert data to a pivot table in laravel using query builder

I'm a beginner, are there any courses or tutorials on youtube where I can learn the following:: One to One Relationship CRUD One to Many Relationship CRUD Many to Many Relationship CRUD That the data is sent over the form and that a query builder ( no eloquent) is used? Thank you very much

Chipsy's avatar
Chipsy's avatar GlenUKLaravel
1

WhereIn in Query doesn't work for array, Any solution?

I have defined scopeFilter in Model which has following code public function scopeFilter($query, array $filters) { $query->when($filters['subjects'] ?? null, function ($query, $data) { //Subjects ids will be comma seperated string eg : 127,125 $subject_array = explode(',', htmlspecialchars_decode($data)); $query->where(funct

manjumjn's avatar
manjumjn's avatar mstraussLaravel
5

Request Implode in query

I am passing my form values to my controller function and i am filtering with the request values and returning to my view. $posts = DB::table('posts') ->join('images', 'posts.id', '=', 'images.post_id') ->select('images.*','posts.*') ->where(array('posts.user_type' => 'isUser', 'posts

Vladjen's avatar
Vladjen's avatar Sti3basGeneral
3

Query from Migration

I need to update the database inside my production environment. I want to expand a table to a many-to-many relationship. In order to do this I need to execute a query to copy rows from one table to another. I don't want to dump the database from production to dev and execute the query's manually. I wonder if I am able to run the particular query from a migration, since it has t

borrie's avatar
borrie's avatar tykusEloquent
3

Sql query works in PDO, doesnt in Laravel with DB

I have a query like that that runs well when i use PDO::MySQL: select max(wartosc) as wiatr, min(wartosc) as wiatr_min, avg(wartosc) as wiatr_avg, data_godzina_minuta from wiatr where created_at >= DATE_SUB(NOW(),INTERVAL 12 HOUR) GROUP BY SUBSTRING(data_godzina_minuta, 1, 14) However when i run it with: \DB::Select(); i get following error Syntax error or access violation:

ensim's avatar
ensim's avatar jlrdwEloquent
2

Changing URL Query shown

Hi, so first of all. Is it recommended to use Laravel as a website framework for blog posts etc.? To view blog posts the usual way is to fetch articles using query URLs such as domain.com/articles/{article-id} to fetch a particular article... However in most cases when I'm reading news etc I see the url as domain.com/articles/this-is-an-article-title... How can I achieve somet

lukegalea16's avatar
lukegalea16's avatar kayschimaLaravel
5

Convert Eloquent Query to SQL

Hello guys, happy new year. Please can someone help me convert this eloquent query to a raw SQL query where I can use in phpMyAdmin to get the same result. The database structure is An employee hasOne profile, the profile hasOne introductionTranslation which translates the introduction text to different languages being spanish and french and just stores the id of the translatio

king_eke's avatar
king_eke's avatar narendraku...Laravel
13

Why 'popular' is not preserved on query string when I change pages

This question is from the 'Hands On: Community Contributions' series, episode 12: 'Sort By Popularity'. When I am at the route: links-sharing.test/community and I typed '?popular' at the end it shows me a list ordered by most popular at links-sharing.test/community?popular. But When I click on a page on the custom pagination it doesn't preserve the '?popular' in the query str

Kenshirou's avatar
Kenshirou's avatar jlrdwLaravel
4

Laravel query scope in related model.

Hello I have Services that have a many-to-many relationship to People as contacts, connected by a services_contacts pivot table and I'm attempting to create a local scope query on the Service model to return the primaryContacts(): public function scopePrimaryContacts($query) { $pivot = $this->contacts()->getTable(); return $query->whereHas('contacts', function

AyobamilayeY's avatar
AyobamilayeY's avatar Ayobamilay...General
4

Can anyone help with this query?

I have three models: User Image Post and their relationship is: User hasMany Image, Image belongsTo User, User hasMany Post, Post belongsTo User, How can I query out the relationship using query builder until I have the data below? user_name: name, image: [ { ... }, { ... } ], post: [ { ... }, { ... } ]```

rezuankassim's avatar
rezuankassim's avatar NakovLaravel
7

Hey guys what's the best way to query a many to many relationship existence?

I have this Playlist class that has many tracks through a pivot table. The inverse is true for the Track class as well. I want to query a list of playlists that have at least one track. <?php namespace App\Models; use Illuminate\Database\Eloquent\Relations\BelongsTo; use Illuminate\Database\Eloquent\Relations\BelongsToMany; class Playlist extends BaseModel {

jgbneatdesign's avatar
jgbneatdesign's avatar jgbneatdes...Eloquent
9

Query builder Laravel

Friends, I have the following query running in my database manager. SELECT us.name, us.id, COUNT(o.id) AS c_orders FROM users us, model_has_roles mhr, orders o WHERE us.id = mhr.model_id and mhr.role_id=2 AND o.user_id = us.id GROUP BY o.user_id The result is as follows: name id c_orders Luis 4 7 Alejandro 10 1 This is my method: public function ge

rjruiz's avatar
rjruiz's avatar rjruizEloquent
2

Format number field in yarja datatables from query

I am writing a searchable table which includes the area and population. Here is the basic query: public function getCountryData() { $co = DB::table('countries')->leftJoin('country_detail','country_detail.country_id','=','countries.id')->addSelect(['countries.id','countries.name','country_detail.capital','country_detail.area','country_detail.iso3','country_detail.pop

jim1506's avatar
jim1506's avatar jim1506Eloquent
19

Laravel complex query issue

Trying to use and/or where statements as if they were nested in parentheses. example: Select * from myTable where (id > 78 or name is not null) and term_date >= NOW()) Trying to get these complex where statements to be used in Laravel Collections like so: $query = myTableModel::where(['id', '>', 78]) ->orWhereNotNull('name') ->where('term_date', '>

SnapKWI's avatar
SnapKWI's avatar SnapKWIGeneral
2