Difficolt Query

Hi! I'm new in Laravel and I need some HELPS for a QUERY. Sorry for my English I have 3 tables: Cables (belongs_to_many Features) Features (belongs_to_many Cables) Cable_Feature (Pivot) with pivot extra field value I have created for everyone a Model with a relationship _(and all works fine...)_ The structure of Table Cables is (Id, Code) The structure of Table Fe

theblack68's avatar
theblack68's avatar theblack68Laravel
7

How to filter data with axios laravel query builder

Hello everyone ! I am stuck at a point and want to know your point of view. I want to create a list and be able to filter the list with multi-select. From this point, controller has been created. Below a summary of my controller $this->per_page = 5; $events = Event::isActive() ->whereDate('from_date', '=', $now) ->whereDat

Kitephx's avatar
Kitephx's avatar KitephxLaravel
0

Why does Query builder add `where 1 = 0` to empty `whereIn('col', [])` clause?

Hi, Just noticed that when I do something like this: \DB::enableQueryLog(); User::whereIn('id', [])->update(['updated_at' => now()]); return \DB::getQueryLog(); The actual query is: update `users` set `updated_at` = ? where 0 = 1 What's the reasoning behind that? Obviously, there's some check whether the passed-in array is empty. What's the benefit of executing a query

devk's avatar
devk's avatar bobbybouwm...Laravel
3

optimize for loop query

Hello Folks , i'm using laravel charts to get some data from databse for last month , so the for loop checkeng each day if there a data or not , i can't find a way to get the same result with less queries , so like images there more than 60 query for 2 charts , so is there any other way to get result with less queries ??

moktar's avatar
moktar's avatar moktarGeneral
6

Not getting any results back from a raw query

Hello, I have a raw query and when I run this query in phpMyAdmin it returns 3 results but when I try to use Laravel Query Builder I get an empty array. My query SELECT id, reply_text, sending_time FROM sms_sender_inbox_replies WHERE phone_number IN ('+1234567819', '+19873216154', '+15984989898') AND id IN ( SELECT MAX( id ) FROM sms_sender_inbox_replies GROUP BY

martinszeltins's avatar
martinszeltins's avatar MartinZelt...Eloquent
2

Query distant (many to many) relationship

Hey folks, I'm stuck with the following. I am building a query based on a filter from a form. One dimension that I need to filter for, is a distant relationship. Here's the concrete example: I'm having posts and cuisine, every posts has one or multiple cuisines (and thus also vice versa). cuisine and post are connected using a pivot table cuisine_post. in my cuisine.php: pu

janhaehl's avatar
janhaehl's avatar janhaehlEloquent
3

Problem with writing query between four tables in Laravel

I have project for ads/properties in Laravel. I am trying on my user profile page to select checkboxes (house, flat, room, etc.) and then to display ads that have those selected values. I have four tables. users (id, first_name, user_preferences) properties (id, user_id, location, price) categories (id, category) category_property (id, category_id, property_id) In categorie

Rakic's avatar
Rakic's avatar RakicLaravel
0

Multiple Find Query in single database connection

$departments = []; foreach ([1, 2, 3, 4, 5, 6, 7] as $department) { array_push($departments, Department::find($department)); } return $departments; How can I make all these multiple query on a single database connection for better performance?

muhammedsellu333's avatar
muhammedsellu333's avatar aurawindsu...Eloquent
6

query nearby restaurants

I need to query nearby restaurants which are 10 metres away how do i go about it?am a beginner using google maps API.Do i need to save the longitude and latitude in their restaurants table?Anyone with a tutorial on that or who will guide me will really appreciate

matildah's avatar
matildah's avatar jlrdwLaravel
2

Activity Streaks: How to return ONLY collection of Carbon objects from query scope?

I'm going to need to compare timestamps from multiple tables, the goal of which is to identify activity streaks. For each table, I can of course grab all of the user's records, pull out the timestamp, add to array, compare, etc... There are a lot of records though, so I'm looking to optimize where possible. An example query scope: public function scopeAnsweredByUser($query,

JoshP's avatar
JoshP's avatar bearcodiEloquent
3

Wrong output in query for id

Hi everyone, I have problem with one query. They give me wrong output of cell. Instead column of "id" ,they put me column "name"? what am I doing wrong? thanks! $task = Task::find($id); $samples = Sample::find($id); $methods = DB::table('methods') ->join('analysis','methods.method','=','analysis.id') ->join('technicians','methods.nam

rogi's avatar
rogi's avatar SnapeyGeneral
8

Handle Query strings in laravel and how secure Are they

i want to know how secure is it to handle post requests with Query strings and if its not secure how can i disable the user to send any query string to my api cause right now the user is able to send any query string regardless of its being right query string or wrong one and in the end can $request get the data from body(From Post man app sending )

farshadf's avatar
farshadf's avatar CobsLaravel
2

How to get this eloquent query to return a builder object

So I have a model with a few hasMany() relationships and I as is normal It returns fine with a collection, since Eloquent rides on top of builder can I get it to return a builder object so other people can chain what they want to it and call get()? Here is My Query: return Company::findOrFail($id)->load('contacts', 'addresses', 'team'); The idea is to put this in a meth

richbreton's avatar
richbreton's avatar sona786General
4

Query scopes and IDE inspections

Hello everyone, I've been thinking of using query scopes on my projects, but so far there's one nasty side-effect to that change: the IDE (in my case PhpStorm)'s inspections flag it as an undefined method. I know that you can disable inspections for those statements, but 1) it reduces legibility, 2) it's extra work I'd rather avoid, and most importantly 3) may prevent actual er

jpinto.ptn's avatar
jpinto.ptn's avatar AccountDel...Code Review
8

query builder search a json column

Hello! In the product table a have a column Colors where i saved the product colors as a json (the colors id`s) Example of data saved in a column Colors: [{"id":"2"},{"id":"4"},{"id":"5"},{"id":"6"}] The query i want to make: $products = Product::query(); if(request()->has('color')){ $color

calin.ionut's avatar
calin.ionut's avatar SinnbeckEloquent
17

How to do this query in eloquent

Can someone convert this mysql query to Laravel eloquent : SELECT * FROM journal where code in (select code from accounts where type=1 or type=3); I tried this but it gives me "Call to undefined method App\Journal::code()" $rslt = Journal::where('journal.branchid', $branch_id) ->whereHas('code', function($query) { $query->where('code', '=', \Account

Zaheer's avatar
Zaheer's avatar ZaheerEloquent
2

Abstracting around the Query Builder for Controller and Jobs

I want to move all the logic of my application into Service classes. Once I do that there should no longer be any usage of the Models outside of the service classes. Currently my app has an API and a console app for running jobs. I see my service classes being able to work with both the api and the jobs by providing the right methods. I want to have on the service class a metho

cristian9509's avatar
cristian9509's avatar cristian95...Eloquent
0

sql query too slow

I have sql table with 2,685,222 rows and have below columns id, codelist_id, ltp, trade, value, volume, date, time, created_at, updated_at. Now When I search something by date, taking too much time. Plz suggest which is faster from below method search by date search by created_at search by id range ( I will make a table for id range for specific date. then from that table coll

arctushar's avatar
arctushar's avatar wilk_randa...Eloquent
7

Query failing on prod server but not local

Hello, I having some trouble with some relation query on prod server. Database and files are the same. On my model Communication I have a polymorphic relationship to the SMSLog model. /** * Get delivered SMS's associated with the communication. * One To Many (Polymorphic) */ public function delivered_sms(){ return $this->morphMany('App\SMSLog', 'model')->where('statu

Charrua's avatar
Charrua's avatar CharruaLaravel
3

Complex query performance

Hey! I need to track statuses on an order model. It's a many to many relationship between order and status, since an order has multiple statuses and a status is used in many orders. Now there is always the "current" and the "next" status of the order. The status sequence is controlled by a "step" integer in the pivot. | order_id | status_id

fabricecw's avatar
fabricecw's avatar DavidPetro...Eloquent
3

Elequent Query Help

I have a table of Employees whom take classes with the following relationships Courses Have Many Classes Classes Have Many Completions Completions Have One Employee Employees Have Many Completions What I need to display is All Required Courses in the thead (Which I have done with this query) $courses = Classes::where('required', 1)->groupBy('course_id')->get(); The tbody

joshblevins's avatar
joshblevins's avatar SnapeyEloquent
1

Best place in project to define query scope if there is no model?

So, I'm working on upgrading an old project to a newer version. The thing is, the project has a certain search filter if I might call it that way. An old collague of mine who built that app made that search filter with nested if statements and it works perfectly but the code is a nightmare. After looking online I found that the most neat way to make that search filter is with a

djolefjc's avatar
djolefjc's avatar JohnBraunLaravel
3

Query filter some time returns null in laravel

I was going through the laravel from scratch episode for archives. the problem i am facing is that some times for some archive dates, no data is fetch even if the record exist in db. the query fired is as below as per the dd(DB::getQueryLog()); array:1 [▼ 0 => array:3 [▼ "query" => "select count(*) as aggregate from `posts` where (`status` = ?)

princeoo7's avatar
princeoo7's avatar princeoo7Laravel
9

Consultation construction Query builder, laravel 5.5

What's up friends. Well, I'm building a query to query builder, I managed to structure it but I have an error that I do not know how to solve. $data = DB::table('usuarios') ->selectRaw('usuario_ad', 'count(usuario_ad) as Conteo') ->whereIn('usuario_ad', function($query){ $query->select('username') -

daniel21gt's avatar
daniel21gt's avatar daniel21gtLaravel
10

Problem with translation of query to eloquent, laravel 5.5

What a companion. I have tried to convert this sql query to eloquen, but I have messed it up when joining another query. select usuario_ad, count(usuario_ad) from usuarios where usuario_ad in (select username from users where users.refer="Thor" union select "Thor") group by (usuario_ad); in eloquen $uno = User::where('refer', 'thor')->select('username')-&

daniel21gt's avatar
daniel21gt's avatar rameezisra...Eloquent
3

Query scope based on multiple conditions

I have a price filter query scope in our product entity that checks if a products price is between the two values inputted. Since we've added discounts to our website, we've been unable to get the query scope working correctly so that it picks either the discounted price or the normal price. Here's what I've written so far: public function scopePriceBetween($query, $min, $max)

LiamMcArthur's avatar
LiamMcArthur's avatar RobstarLaravel
3

Laravel API Query String Route

Hi! I'm fairly new to building APIs using Laravel, and I was wondering why when I use query strings in postman, the data returned will always be from my index method. Meaning, when I go to (.../api/this_table?status=Typed), instead of getting all columns of "this_table" where their status is only "Typed", i instead receive all of the columns of "this_ta

ignisrzeus's avatar
ignisrzeus's avatar tykusLaravel
6

Error log saying no query results found but there are records

I just don't understand this error. I'm working on an app where I download records from a server and store them in a temporary table called rets_property_res_naples and then in a console command, they get selected, transformed a bit, then put into a "master" table. I'm now getting the below error and I just don't understand it. If I go into php artisan tinker and do a

ChrisF79's avatar
ChrisF79's avatar TalinonGeneral
9

How to convert following sql query to laravel query buillder?

$sql = "SELECT id, name, ( 3959 * acos ( cos ( radians(?) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(?) ) + sin ( radians(?) ) * sin( radians( latitude ) ) ) ) AS distance FROM branch HAVING distance < ? ORDER BY distance LIMIT 0 , 20"; $data = \DB::select($sql,[$latitude, $longitude, $latitude, $distance]); I am trying to get all the

ssquare's avatar
ssquare's avatar kimaLaravel
8

Laravel Query Doesn't Retrive the given value

I got an random array instead of value am actually passed on where condition, my Actual query is $data = Dataimp::where('name','Tesoro'); dd($data->toSql(),$data->getBindings()); My Raw Query & Binding data is as follows "select * from "data_dial" where "name" = ?" Builder {#373 ▼ #passthru: array:12 [▶] #query: Builder {#371 ▶} #model: D

Tesoro's avatar
Tesoro's avatar NakovLaravel
4

Dynamically assign ID of query to anchor id attribute

I am populating a DataTable in Laravel with the results of an Inner Join query. After, that I am generating update buttons which will allow me to edit the selected record. I would like to use the id of each record returned from the Join query to be unique row id. The issue is, the query returns an array of objects so I am not sure how to access each id. I've tried using a forea

Kish77's avatar
Kish77's avatar Kish77Laravel
1

Help convert query builder into Eloquent

Simply put, I am trying to retrieve all the favorites a user has made. Then fetch the associated reply on which the favorite has been made, then the associated thread in which the reply has been made, then the channel in which thread has been made. I tried setting up eloquent relations but I've failed so I resorted to a query builder, which works as you'd expect. $favorites = D

ufodisko's avatar
ufodisko's avatar ufodiskoLaravel
2

Authorization and mysql query

Hi everyone, I'm not sure where to post this, I've got no problems but I'd wonder if I could optimize my code, so here is the thing. I've got a model called 'Promotion' which is a sort of a container. To view a promotion a user must have the authorization according to his role and his company. For the promotion owner this is just a simple verification like promotion->owner_i

Ashraam's avatar
Ashraam's avatar AshraamLaravel
2

Relationship from query?

Hi there, I've got this model method that I would like to convert into a proper relationship. Is that possible? Basically I want to convert the query into a HasOne... public function bestOffer() { return $this->hasMany(Offer::class)->orderBy('amount, 'desc')->limit(1); }

DerManoMann's avatar
DerManoMann's avatar DerManoMan...Eloquent
7

Having problems with query cache

Hello, I am trying to display random query but to stay it in cache so it wont refresh when reloading page. I am using rememberable so I have remember() function working like it was in older versions. But even if I use Cache::remember I have same problem, In the limit I have selected 1 and on my website I also have message Showing 1 to 1 of 1 entries but actually I see everythin

doctornasty's avatar
doctornasty's avatar doctornast...Laravel
4

Laravel 5.7 selectRaw array binding is not working (in mySQL full text search query)

I am using mySQL full text search and the column "nimi" is indexed accordingly. An hardcoded query, which injects the search term directly into SQL, is working properly: ... $term = '+mysearch*'; $result = DB::table('register') ->select(['nimi', 'kood']) ->selectRaw("MATCH(nimi) AGAINST ('" . $term . "' IN BOOLEAN MODE)

milosh's avatar
milosh's avatar miloshGeneral
7

Query builder to collection of models.

I had just made some changes to my database and now the relationships are more complicated. This means that to get $user->reports I am now using the query builder to retrieve the reports which were previously just a many-to-many: $results = DB::table('users AS u') ->select('r.*') etc This means that I am now getting a collection of stdClass instead of a col

phpMick's avatar
phpMick's avatar phpMickGeneral
3

Slow query when using string instead of integer (implicit conversions)

I have two almost identical queries. select count(*) from `master_transactions` where `master_transactions`.`settlement_id` = '11839491331' and `master_transactions`.`settlement_id` is not null and `settlement_type` = 'amazon_settlement' and `sales_clearing_invoice_id` is null select count(*) from `master_transactions` where `master_transactions`.`settlement_id` = 11839491331

booni3's avatar
booni3's avatar lambooniLaravel
3

Optimizing Eloquent Query

I have an autocomplete text box that works well. I need to increase speed of the drop down list. Its too long - you can finish typing before it suggest anything. I have indexed the table and no improvements. So right now I just want to break down the problem into smaller pieces. Lets for now look at the eloquent query itself. Is this the most optimal I could have written it? I

screwtape_mk's avatar
screwtape_mk's avatar SnapeyLaravel
27

Start new Query Builder on Model's table

Hi guys, is there any helper method to start new Query Builder on Model's table? I need to return some raw data to get a pure Collection, not Eloquent Collection of models. In other words, I'm looking for a helper returning DB::table((new MyModel)->getTable()). I know there's a query() method on Model, but it doesn't do that. It just starts new Eloquent Query.

RoboRobok's avatar
RoboRobok's avatar RoboRobokEloquent
23

Query relationships in Laravel

Hi All, I'm building a voucher system. Tables: Vouchers(id, sender_id, receiver_id, cart_id) Carts(id, brand_id, city_id) LineItems(id, cart_id, product_name, mrp). class Voucher extends Model { public function cart() { return $this->belongsTo('App\Models\Entities\Cart'); } } class Cart extends Model { public function vouchers() { return $this-

Saneesh's avatar
Saneesh's avatar SaneeshLaravel
7

Query to select data from database table and array

How can I select data with raw query from data that exists both in database table and an array? For example I want to store to $sc the result of the following query. The variable $cs is an array which has data from previous query execution. $sc = DB::select(DB::Raw("select table1.object from table1, today_date where today_date.today <= table1.expiry_date and today_date.

konrms's avatar
konrms's avatar konrmsEloquent
2

Eloquent query with 3 tables

I have 3 tables: exercises, sets and set_exercise. exercises and sets provide the simple lists of sets/tables. set_exercise defines which exercises belong to which sets (using ids, so set_id and exercise_id). In this table also, each row contains some additional data for a given row. How do I get the list of all exercises with this additional data the best way (ideally using a

Benko's avatar
Benko's avatar BenkoEloquent
2

how to send auth api token in query body not in url params

works only if i sent it in paramter Not Working If i sent in in URL query body I'm using post man method get

Respect's avatar
Respect's avatar RespectLaravel
5

Convert SQL raw query to Laravel ORM or Query Builder

Hi - I have some raw queries I'm getting stuck while converting them into Laravel query builder or in ORM. If someone can please convert them thank you. Today : select distinct id, ip_address, browser from table_name where DATE(visited_time) = CURDATE(); Month : select distinct id, ip_address, browser from table_name where month(visited_time) = month(NOW()) and year(visited_t

JavedBaloch's avatar
JavedBaloch's avatar Tray2Laravel
3

mysql query escape single quote

Hello, i have a problem with this following db::insert command. I want to use array in a mysql query "in ()" section, but the commas has been escaped -> in ('435','1671','429') How can i solve this problem? <?php $users = [435,1671,429]; $list = implode("','",$users); DB::insert("insert into ". "rejections (calendar_id, user_id, not

rotkivagrav's avatar
rotkivagrav's avatar SnapeyLaravel
2

User::create() results No query results for model [App\User].

I run the following method and for some reason, it makes the table, but then right after it throws an error. public function create_user(){ $user = User::create([ 'first_name' => $this->first_name, 'last_name' => $this->last_name, 'email' => $this->email, 'password' => bcrypt($this->password)

maidul98's avatar
maidul98's avatar SnapeyCode Review
8

How to search through two tables in one query?

I am trying to search through two tables in one query, as a User, who hasMany friends (User.php), and doctors (Doctor.php). Unfortunately the following logic is not working. I get a blank collection event though there must be at least a friend within, as I tried the solo-query for the friends before. $month is a simple input request to check on the date within the friends, and

kendrick's avatar
kendrick's avatar splendidke...Laravel
14

groupBy() eloquent query

My current eloquent query is working fine in general: $userProfile->confirmedProducts()->whereHas('orders', function ($query) { $query->->finalized()->groupBy('product_id'); })->take(5)->get(); It gets the confirmed products of a user, now it fetches all orders of the products, only the 5 products with the most finalized orders should be listed by the

t0berius's avatar
t0berius's avatar SnapeyEloquent
10

Issue with encryption decryption in Laravel Query Builder

I am working on a new project that stores medical related information. We are working on a project that stored Healthcare related information. For Security reason we store users table fields value with encryption, to implement that we use trait that encrypts and decrypts on some selected fields of User Model and its working fine. Now I want to apply a filter on that encrypted d

rajdipbacancy's avatar
rajdipbacancy's avatar packyGeneral
8