Help me rewrite Eloquent (Query builder)

I have working script. With table: RECORDS: id|name|text|latitude|longitude return $builder->whereRaw('ST_Distance(point(latitude, longitude), point('.request('latitude').','.request('longitude').')) < '.$distance); But I moved latitude/longitude into separate table, so I have: RECORDS: id|name|text|city_id CITIES: id|latitude|longitude How do I rewrite the query build

andyandy's avatar
andyandy's avatar andyandyEloquent
7

Query Builder clauses

I need help. Would like to include clauses in a query. $orders = DB::connection('sqlsrv1') ->table('PF_tank_gas_orders') ->join('KHKAdressen', 'PF_tank_gas_orders.adress', '=', 'KHKAdressen.Adresse') ->join('ECCTanks', 'ECCTanks.Tank', '=', 'PF_tank_gas_orders.tank') ->where('KHKAdressen.Mandant

Nosean's avatar
Nosean's avatar SinnbeckLaravel
2

No query results for model [App\Models\Appraisal\AppraisalRespondent] msf_manager

I have this code: Controller use App\Models\Appraisal\AppraisalRespondent; public function msf_manager() { $userCode = Auth::user()->employee_code; $linemanagerEmployeeIds = HrEmployee::select('id','employee_code')->where('line_manager_id', $userCode)->get(); $respondents = AppraisalRespondent::orderBy('created_a

noblemfd's avatar
noblemfd's avatar MichalOrav...Laravel
5

How to get full URL without sorting query parameters

I want to extract the query part of a URL, this is my URL for example http://mywebsite.com/tvbet/response?action=balance&refId=0&accountId=1&gameId=100138&clientType=desktop&currency=TRY&isBonus=False&username=Yonetim&hash=iobVJKHFhhgjvfcVJHIKVGYFG To do that, I am using laravel helper url()->full() . Now the problem is that it is re-arrangin

SimonAngatia's avatar
SimonAngatia's avatar SimonAngat...Laravel
4

Use Query Builder to select from two subqueries

I'm at a loss on how to duplicate the following query using the query builder: select ii.total_in, io.total_out, (ii.total_in - io.total_out) as QTY_ON_HAND, ii.warehouse_id, ii.part_id from (select sum(it.qty) as total_in, it.warehouse_id, it.part_id from INVENTORY_TRANS it join PART_SITE ps on it.PART_ID=ps.PART_ID where it.type='I' and it.PART_ID = '46277' and it.TRANSACTIO

grozavule's avatar
grozavule's avatar ridorexLaravel
2

Elasticsearch query has stopped working after 7,7

This was working on 7.6.2 but since upgrading to 7.7 it has stopped working and do not know why? I am doing a query with a nested or with a nested must so it has to be 5 5 5 or 6 6 6 on three columns. I am using the laravel scout driver for elastic search babenkoivan/scout-elasticsearch-driver Thanks :)! "query": { "bool": { "must": [

forefront's avatar
forefront's avatar forefrontCode Review
1

Poor query performance with `whereHas()`

I've been battling with this problem now for a couple of weeks. I have a Laravel 7 application that seems to be absolutely hammering the database when performing a certain query. First I'll outline the problem and then dive a bit more into what I've tried to isolate it. I have an Opportunity model that has a scopeLucrative() scope on the model that filters the opportunities to

AlexG834's avatar
AlexG834's avatar AlexGodbeh...Laravel
1

author and product relationship query issue

I want to get all author with their product at least 5 but the query is not working $users = User::with(['products' => function ($query) { $query->limit(6); }])->get(); the above query will return something like below Collection {#382 ▼ #items: array:5 [▼ 0 => User {#412 ▼ #fillable: array:3 [▶] +sortable: array:3 [▶] #hi

tehseen's avatar
tehseen's avatar GeordieJac...General
14

Getting my friends and friend requested and not my friends on a query

Hi there, I am using this package rennokki/befriended https://github.com/renoki-co/befriended#filtering-followedunfollowed-models And I implemented a search functionality on my React native app. But I want to get the requested friends and my friends also not my friends. this is my controller namespace App\Http\Controllers\User; use App\Http\Controllers\Controller; use Illumina

Mohammed's avatar
Mohammed's avatar MohammedGeneral
1

Query for ajax added items

Hello everyone, I have elements in one page: consul date Each page is about a video game, so the number of consoles is variable, I in the blade have: @foreach($giochi->infogiochi as $info) <div id="stxrelease" class="form-group card col-12"> <div class="card-body"> <h3>Data di rilascio</h3> <div cla

Andreas94's avatar
Andreas94's avatar Andreas94General
3

Google API to get google query search results with Laravel 6+ for free?

I already enabled billing in my Google API services, I want to be able to use google api search in laravel to query results as if an user was to use the classic google searchbox, I want to atleast get 10 url results. What is the best and easier composer package for doing this for free? Is such a thing even possible to do with php?

Gabotronix's avatar
Gabotronix's avatar automicaGeneral
1

Is there a way to query a pivot table in the DB builder(Not eloquent)

Hello, I am looking at the doc and I cannot find a way in the DB query pages to get the data from a pivot table. Any idea how to do this please? Is there not something like ->wherePivot(tablename, id, id) ? Thank you.

LaraBABA's avatar
LaraBABA's avatar BoubouLaravel
3

How to make efficient query for $post->commnets and comment->user

Hello, I have a Post model which hasMany Commnets and every comment has one User. If I run this blade code @foreach( $post->comments as $comment ) <div>{{ $comment->text }} <br><small>{{ $comment->user->name }}</small></div> @endforeach it makes one separate query for user in every loop of the foreach cycle. I know about Post::wi

Čamo's avatar
Čamo's avatar ČamoEloquent
4

Limiting where query in laravel

database table name=dbtable column names of the table=gender,carName,city. I want to get 1000 data from the table. But 500 of the gender column will be female and 500 will be male. will get 300 honda, 200 jeep, 500 fiat from the car column And 100 Istanbul, 200 Paris, 200 Milan and 500 London from the city column. how can i do this in laravel sql query? thank you so much in adv

metin's avatar
metin's avatar automicaGeneral
5

query where cluase is pulled from a relation

I have a user table, a credits table and a region table. each credit is defined to a region by region_id in credits table, a User can have more than 1 region assigned to it. What i am trying to achieve is to get all credits that are assigned to a user, so i have to cycle somehow through all of the user;s regions to include them in the query, $pagination = $query->where(funct

pllaguno's avatar
pllaguno's avatar squiaiosEloquent
2

Compare sum of sub query column to parent column

I've been banging my head against the wall on this all day (on the verge of literally at this point). I'm terrible at SQL. This query is actually a sub query itself taking place in its parent model scopeQuery. I've got a charters, trips and a bookings table. I need to return charters with trips that the sum of all the trip's bookings column bookings.passenger_count does not exc

ChrisArter's avatar
ChrisArter's avatar ChrisArterEloquent
1

Nested Query Help

I have a Laravel app that allows users to login and take a COVID-19 self-attestment survey to record their temperature and answer questions recommended by the CDC, the HR manager would like a report of all users missing surveys between the dates she selects(omitting weekends). I have the "hasMany" and "belongsTo" relationships defined. I'm just not sure how

gregupton's avatar
gregupton's avatar Natcoffolo...Laravel
4

DB::table query error

I'm trying to run a query with orderBy using DB::table('subjects')->orderBy('name', 'desc') but it throws an error Undefined property: Illuminate\Database\MySqlConnection::$id. If I do query like this Subject::all() or any of these - it works fine. I tried with use DB or use Illuminate\Support\Facades\DB but none of them work. My model is Subject and table is called subjects

redlik's avatar
redlik's avatar tashariEloquent
3

Sql query

Hi, here is sql query: select `attribute_values`.`id`, `attribute_values`.`title` as `theme_title`, ( SELECT SUM(product_variants.inventory_quantity) FROM product_variants WHERE product_variants.product_id = attributables.entity_id ) as stock, ( SELECT SUM( CASE WHEN

cipsas's avatar
cipsas's avatar cipsasEloquent
0

Conditional Where Clauses using Query Builder

I have one query that is dependent on the results of another. First, I run the following query against a SQL Server DB table: $variableRevision = DB::connection('engineering') ->table('VariableValue as vv') ->join('Documents as d', 'd.DocumentID', '=', 'vv.DocumentID') ->select('VariableID', 'max(RevisionNo) as MaxRevisio

grozavule's avatar
grozavule's avatar grozavuleLaravel
2

Passed a var to second query

Hi, I have a query like this: public function getInstalledHostsSoftwareProduct($id) { $data = Host::whereNull('deleted_at')->whereHas('softwarelicense', function ($query, $id) {return $query->where('softwareproduct_id', '=', $id);})->with('hosttype')->with('manufacturer')->with('office.site')->with('enduser')->get(); return response(

maltekiefer's avatar
maltekiefer's avatar SinnbeckLaravel
1

How to prioritize eloquent query to create granular searches?

Hi all, I am a bit stuck on this one..... Imagine a form with 3 dropdown buttons. color(black,white), size(big,medium,small) and material(plastic,metal,wood). All choices are from related tables. All choices are multiple select choices (array []). Table "productss" with: id color_id size_id material_id Each id is related to another table (hasMany) $resul

LaraBABA's avatar
LaraBABA's avatar hallindavi...Laravel
1

Query by related model attributes

I have Post and Category models. One post can have many categories so I have many to many relationship stored in table named postsCategories. postsCategories table has the following attributes: id, category_id, post_id Post model: public function categories() { return $this->hasMany('App\PostCategory', 'post_id', 'id'); } Now, in my controller I hav

mleontenko's avatar
mleontenko's avatar SinnbeckEloquent
2

Search and Filter Query with Jscroll

I am setting up a listing platform and so far i have made the structure. The listings are shown in a view and integrated with Jscroll. The listings view has a filter as shown below. I filter the request by using when in the Model query and orderBy in the end with pagination. However the orderBy lasts for first pagination results page and not return intended results after the s

Tithira's avatar
Tithira's avatar TithiraGeneral
2

I need equivalent eloquent query

I want to run this query with eloquent. SELECT * FROM users WHERE id IN (SELECT worker_id from projects up WHERE (SELECT COUNT(*) from projects below WHERE status = 2 and below.worker_id= up.worker_id) > 2 GROUP BY below.worker_id) Below is the attempt which fails and the issues is how to use the count within the subquery as it up in the pure mysql query. User::whereIn('id',

beyond's avatar
beyond's avatar jlrdwLaravel
7

duplicated query

i have in my front end template two menus, one for mobile and another for desktop, I have foreach loop for every menu with the categories, is there a way to not use the query two times ? "for better speed loading"

kaiserkais's avatar
kaiserkais's avatar MichalOrav...Laravel
1

Trying to add a join to a model query?

I've got this model that query's my volunteer table, I'd like to relate it to my job_positions table public static function search($query) { return empty($query) ? static::query() : static::where('related_experience', 'like', '%'.$query.'%') ->orWhere('firstname', 'like', '%'.$query.'%') ->orWhere('lastn

Aron-Spiess's avatar
Aron-Spiess's avatar Aron-Spies...Eloquent
3

Vue this.$route is NOT picking up other query parameters

I just noticed there is something wrong with Vue. when you console.log this this.$route.fullPath. this is the result users?page=3&per_page=15&name=&display_name=&order_by=desc BUT when you console this.$route.query this only contains { display_name: "", name: "" } page, per_page, and order_by is not in the query object.

skeith22's avatar
skeith22's avatar skeith22Vue
3

Where query with multiple conditions ?

Hello, Is it possible to use the where query with multiple conditions ? For example I want to query the list of the challenges only if is_visible_at <= Carbon::now() or if is_visible_at = null. I have tried with where()->orWhere(), but it doesn't work. Thanks for your answer. Vincent

vincent15000's avatar
vincent15000's avatar vincent150...Eloquent
3

Query polymorphic AND one to many at same time?

I can retrieve polymorphic data by doing this: $users = User::with('activity')->get(); Not all users are employees, but all employees are users. Here is the query for that relationship: $employees = DB::table('employees') ->leftJoin('users', 'employees.id', '=', 'users.id') ->select('users.name','employees.*') ->get(); Now, how to combine

ajsmith_codes's avatar
ajsmith_codes's avatar ajsmith_co...Laravel
1

How would I create this query based on a method I have?

I have a site I'm working on that will find music teachers near students. The student can look for teachers by choosing a distance from their address. I have a method calculate() that will take two latitude/longitude coordinates and calcualte the distance between the two points. It works great. How do I create a query that will find all of the teachers with lat/long within a di

ChrisF79's avatar
ChrisF79's avatar edocGeneral
2

Auth::user() query execution time.

Hi, guys. Need your advice. I am getting current authenticated user in controller method, by following code. $authUser = Auth::user(); echo $authUser->name; and in debugger console, I get this :- select * from `users` where `id` = 25 limit 1 12.22ms \vendor\laravel\framework\src\Illuminate\Auth\EloquentUserProvider.php:52 Why this query is taking so much time to exec

ssbrar's avatar
ssbrar's avatar jlrdwLaravel
5

Laravel Query Builder, I Need Help in Write Query

I have a problem can you people help me to getting out from this problem. I have table of patients. here is table schema. Schema::create('patients', function (Blueprint $table) { $table->id(); $table->string('first_name'); $table->string('last_name'); $table->string('slug')->nullable(); $table->str

Aqeel94321's avatar
Aqeel94321's avatar rodrigo.pe...Laravel
9

Validating Signed Route Requests with additional query parameters attached to the end of the signed URL

Hi, I am creating a signed URL to a name route (the route is named verify). To create the URL in a controller, I am using: $url = URL::temporarySignedRoute('verify', now()->addMinutes(30), ['id' => $id]); When returning this signed URL and inputting it into the search bar, the URL is validated successfully; However, when sending this ULR using Sendgrid, additional query p

BrianA's avatar
BrianA's avatar lakmLaravel
9

Laravel Eloquent Query Passing Variables

I have a query that gives me a very large dataset of invoices. I am trying to get invoices only in a particular category using the where clause by passing a variable called lab_tests that contains all the category items . I keep getting this error SQLSTATE[HY093]: Invalid parameter number: parameter was not defined Is there a better way to pass the variable or filter out the

shiro_'s avatar
shiro_'s avatar SinnbeckCode Review
1

Query to test if a row exists ?

Hello, I have a many-to-many relationship between two tables. public function users() { return $this->belongsToMany('App\Models\User', 'users_games') ->withPivot('score') ->withTimestamps(); } I want to test if a user is registered in a game. So I added this method. public function isCurrentUserRegistered() { $count = $this->withCount(['user

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

query on tables with relationships and a condition in a pivot table

Hi I ve these tables with relationships Customers namespace App; use Illuminate\Database\Eloquent\Model; class Customers extends Model { public $primaryKey = 'id'; protected $fillable = [ 'contr_nom', 'contr_cog', 'benef_nom', 'benef_cog', 'email', 'polizza', 'targa', 'iban', 'int_iban', 'cliente', ]; public function claims() { return $this->h

NiloLeon's avatar
NiloLeon's avatar NiloLeonEloquent
8

Laravel 7.2.5 finds duplicate aliases in mixed DB Query

maintaining some code that has DB queries that mix DB::raw with dbQuery. When a field is selected as an alias in a DB::raw query and then combined with dbquery left joins in Laravel 7.25.0 - it may complain that it has encountered a duplicate value for one or more of the aliases. It appears to work without error in Laravel 7.2.0

nalexander's avatar
nalexander's avatar nalexanderLaravel
2

Adding multiple dynamic conditions to a query

Hi all, I am trying to filter the output of a certain model for my blade view. Meaning, in my Controller I do the following: $query = Company::query(); if ($request->locations != null){ $id = $request->locations; $query = $query->whereHas('locations', function($q) use ($id) { $q->where('location_id', '=', $id); }); } elseif ($request->locationMandato

acrm's avatar
acrm's avatar acrmLaravel
7

Eager loading hasOne -- one query, but loads all models into memory?

Hi Guys! I'm trying to eager load a model, in this case, specifically, I want to eager load the "latest" of a hasMany relationship, for example: User class: public function posts(){ return $this->hasMany(Post::class, 'author_id') } public function latestPost(){ return $this->hasOne(Post::class, 'author_id')->latest(); } Controller:

zack6849's avatar
zack6849's avatar zack6849Eloquent
6

Insert into... query problem

Sorry but this is a general MySQL question and not strictly Laravel related. But I wouldn't ask anywhere else as this community is the best. I'm trying to INSERT INTO by using this query INSERT INTO songboxes (plays) select count(`songbox_id`) from activity_updates as au join songboxes on au.songbox_id = songboxes.token where `status` = 1 Group by songbox_id I am getting the

Mick79's avatar
Mick79's avatar Mick79General
7

Eloquent Sub Query

I have a polymorphic table called productables it laid out like: https://share.getcloudapp.com/z8uZZYeR product_id is linked to my products table A product can be added to a diary and/or a product can be added to a week If I want to grab all products that belong to that diary/weeks I am trying a query like this: $weekIds = $diary->weeks->pluck('id'); //(this return an a

datarecall's avatar
datarecall's avatar datarecallEloquent
28

Query all records in table where there isn't a match in the other?

For a one-to-one relationship, how do you query only the records without a match? I see that in MySQL you can't use EXCEPT. I also tried looking at the various joins. Users Table: id name Employees Table: user_id I only want the results from the users table that are not linked to the employees table.

ajsmith_codes's avatar
ajsmith_codes's avatar ajsmith_co...Laravel
3

Where query column if column has value

Hi everyone, please help to solve below issue There is table with column date_from and date_to. date_to column is optional, so it might be null. I want to make query this column if it has value, if it is null no need to run query at all. Is there way to make query conditionally depending column value? I have tries as below but it does not work as expected. trips()->with('use

Dosmukhanbet's avatar
Dosmukhanbet's avatar MichalOrav...Eloquent
5

Query model when column not null

hello, Please could somebody help. I have date_to column, it can be null and I wanna add where query when this column not null but following code does not work $this->trips()->where( function ( $query ) use ( $date ) { $query->whereNull( 'date_to' ) ->orWhere('date_to', '<=' ,

Dosmukhanbet's avatar
Dosmukhanbet's avatar MichalOrav...Eloquent
14

convert a sql query to php laravel query

I am new at laravel so i want to convert a query to php laravel query. SELECT c.id ,c.name,c.image,c.status,count(pv.id) as product_varients_count, (select count(*) from subcategories as sc where c.id = sc.category_id ) as subcategories_count, (select count(*) from products as p where c.id = p.category_id ) as products_count FROM `product_varients` as pv left join prod

lavnishad93's avatar
lavnishad93's avatar MarianoMor...Laravel
8

How to get more than one value from a Database query?

Hello guys. I have created the following db query using the Laravel DB Facade : DB::table('currencies')->whereNull('spot_price')->value('shortcode'); The problem with the query above is that I only get one value (for example 'ABC') and I have two records in my table that match these specific criteria. Any ideas on how to get multiple values back? Thank you very much!

skiab0x's avatar
skiab0x's avatar MichalOrav...Laravel
1

Forms, Query Strings, and Controller Parameters

I have an HTML form that submits a GET request. Below is the Blade template for it: @extends('base') @section('content') @error('serial') <div class="alert alert-danger">{{ $message }}</div> @enderror <div class="row"> <p>Type a part number into the box and press "Generate Report" (Example:

grozavule's avatar
grozavule's avatar SnapeyLaravel
4

How to make such a simple SQL query

I am passing a context_id, let's say 12345 to a MYSQL query. I have two tables. Table A: contains column user_id & context_id. Table B: user_id, name etc. Now i need to search in Table A for context_id 12345 which i passed to get the user_id from Table A. With this user_id i want to get every record from Table B which is equal to user_id. How would i make such a call as a b

christopher's avatar
christopher's avatar SinnbeckGeneral
2

Query builder with join on find_in_set

Could someone explain how to build a query with a join using a function? The query I'm trying to replicate is: SELECT DISTINCT f.id FROM spDiary AS d INNER JOIN spFacilities AS f ON FIND_IN_SET(f.id, d.facilityid) WHERE d.schoolid = 7 && (d.timeslot_start < '2020-07-01 20:00:00' && d.timeslot_end >= '2020-07-01 19:00:00') using the following... $f

jerrywright's avatar
jerrywright's avatar jerrywrigh...Laravel
2