Need Help with DB Query for geo search.

I have a client that is on a shared hosting plan and i have a problem. He cannot upgrade to a plan that will allow us to set mysql globals. My problem is i need to be able to run this I cannot. Here is what i need to run and cannot. SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); Here is the query we need. Can anyone help me with a alternative rega

madsynn's avatar
madsynn's avatar madsynnLaravel
5

String contain alphabetic or not in query

Hello , I have one laravel query in which I have to get those record , which column data have not any alphabetic $getData = User::where('storeName', 'LIKE', '%name%') ->where('storeFile', 'LIKE', ???) ->get(); Here, 'storeFile' column have data which contain numeric as well as alphabetic. I want those record which have not contain alphabetic . For example : 'st

rushh's avatar
rushh's avatar rushitaLaravel
4

Query in service constructor

Hello, I'm facing a problem with a query in a service constructor. I use this service to connect an API, and in this constructor I get the token which is in my database. This way, I don't have to fetch the token every times I want to fetch data from this API in other services. However, when I pushed my code on a dev server, php artisan migrate can't run because table which stor

stanislasmm's avatar
stanislasmm's avatar martinbeanLaravel
2

Telescope shows query escaped incorrectly

Please observe the following query, equivelant to the user input being Crotchet's Entity::selectRaw('MATCH (name, seo_title) AGAINST (?) AS relevance', ["Crotchet's"])->get(); Telescope shows the query as: select MATCH (name, seo_title) AGAINST ('crotchet\'s') AS relevance from `entities` where `entities`.`deleted_at` is null All good so far. However, let's

Garet's avatar
Garet's avatar GaretLaravel
0

Many-to-many query help required!

I have 4 models with the relevant migrations / tables: -topics -question_topic -questions -responses Topics and questions are linked via the intermediate questions_topic table in a many-to-many relationship. Questions are related to responses in a one-to-many relationship. I need to be able to show a random selection of x questions for a specific topic and the related response

simon_eds's avatar
simon_eds's avatar simon_edsEloquent
4

Query Builder multiple sub queries

Any ideas how use query builder with this query? SELECT * from (SELECT * from table2) t1, (SELECT * from table2) t2, (SELECT * from table3) t3, (SELECT * from table4) t4

_NinjaDev's avatar
_NinjaDev's avatar kimaLaravel
7

How to convert this query in eloquent

I would like to convert this query in eloquent $lims_product_data = Product::where('code', $product_code[0])->first(); and add a extra condition, that will be a not in clause to get all the products in the procut table but execept the products with category_id = 5. i do that in mysql with the query SELECT * FROM products where is_active=1 and category_id NOT in (5) The first

Victor Alfonso's avatar
Victor Alfonso's avatar Victor Alf...Eloquent
4

Help with query and subquery

Hello! I have a problem when I'm trying to pass a query with subquerys to Eloquent. I'm using Laravel 5.3.31 and the IDE crashes in the line })->on('Trackings.user_id', '=', 'u.id');. This is the query: SELECT u.id, u.name FROM users u INNER JOIN perfil p ON p.user_id = u.id WHERE p.user_id = p.empresa_id AND p.activo = 1 AND p.user_id NOT IN ( SELECT uem

FerranMunoz's avatar
FerranMunoz's avatar aurawindsu...Eloquent
1

No query results for model [App\Thread] favorites

I have a thread where I can favorite the thread so I created a polymorphic relationship between the two of them(as replies of a thread also can be favorited). I wrote a post and delete request for the thread in my vue: methods: { toggle(){ // check if thread is favorited if(this.isFavorited){ axios.delete('/threads

CookieMonster's avatar
CookieMonster's avatar nickywan12...Laravel
4

How can I edit this query?

I get the correct result as in CodeA, but since I do different filters with $ query, I have to use it with $ query in a structure like CodeB. How can I do that? CodeA \DB::select("select tableA.*, tableB.price, tableB.diff from tableA right join(SELECT id, end_date, pro_id, price, DATEDIFF(tableB.end_date, '2021-03-08') diff FROM tableB GROUP BY id order by diff asc) table

acetas's avatar
acetas's avatar acetasLaravel
2

How can I write this sql query in Laravel?

How can I write this sql query in Laravel? http://sqlfiddle.com/#!9/1249d9/30 select `tableA`.*, `tableB`.`start_date`, `tableB`.`end_date`, `tableB`.`price` from `tableA` right join( SELECT id, start_date, end_date, pro_id, price, DATEDIFF(`tableB`.`end_date`, '2021-03-07') diff FROM `tableB` GROUP BY id order by diff asc ) `tableB` on `tableA`.`id` = `tab

acetas's avatar
acetas's avatar traster100Laravel
5

How to count and do alias in query builder ?

How i can do this in query builder laravel ways ? "select COUNT(DISTINCT idm) as total, COUNT(idm) FILTER (WHERE nilai=1) as jawab_1, COUNT(idm) FILTER (WHERE nilai=2) as jawab_2, COUNT(idm) FILTER (WHERE nilai=3) as jawab_3, COUNT(idm) FILTER (WHERE nilai=4) as jawab_4, COUNT(idm) FILTER (WHERE nilai=5) as jawab_5 from t_mahasiswa_quisinoer WHERE tahun_id='aaa' and kode_m

karapho712's avatar
karapho712's avatar neilsteeEloquent
3

Sending email during Import: No query results for model [App\User]

I have the following Import function: public function collection(Collection $rows) { foreach($rows as $row) { if(User::where('email', $row['email'])->first() == null) { $user = User::create([ // 'name' => $row['name'], 'email' => Str::lower($row['email

twoarmtom's avatar
twoarmtom's avatar twoarmtomLaravel
8

writing query inside map

i am trying to display category based exam stats display using below query, $categoryDetails = QuestionCategory::whereIn('question_category_id',$categories)->get(); $sortcollection = $categoryDetails->map( function($category) use($test_id,$user_test_id,$user_tests_det) { $catcorrectCount = UserAnswer::join('questions','user_answers.question_id','=','

Deekshith's avatar
Deekshith's avatar DeekshithLaravel
2

Laravel query but works with phpmyadmin

Hi, I've got the next query that works fine with phpmyadmin: select COUNT(networks.hardware_id) AS numAddresses, hardware.id,name,workgroup,userid,ipaddr,ip_ranges.centre,osname,oscomments,processort,processorn,processors,memory,lastcome,deviceid,useragent,bios.smanufacturer as biosmanufacturer,smodel as biosmodel,ssn as biossn,bios.type as biostype from `hardware` inner join `

mp3man's avatar
mp3man's avatar mp3manLaravel
3

foreach array wise multi SQL query not working in PHP

I am trying to execute multiple queries with the multiple selected checkbox value-wise in PHP. I am facing trouble that my code is executing only one checkbox value-wise query and the rest is denied. I checked on StackOverflow about this issue and I got lots of threads about foreach loop but in my case, it is not working when I am applying that. Please help me, I am first time

UsmanBasharmal's avatar
UsmanBasharmal's avatar UsmanBasha...Laravel
1

Laravel - query table array of ids

Hello guys, I'm trying to query a table with an array of ids, but for some reason It will only show one. I have the following $headerpayload = DB::table('netsecure_payload')->pluck('payload_id')->all(); Which outputs the following array:5 [▼ 0 => 1 1 => 7 2 => 8 3 => 24 4 => 25 ] I need all these values to query the next table for all the data

dk4210's avatar
dk4210's avatar dk4210Laravel
7

make another query if field have specific value

i have field named arrive_location arrive location have just two value (exist, new) in eloquent $orders = Orders::select('id', 'users_id', 'arrive_location') ->with('users:id,fname,lname,telno') i want if arrive_location == new, to make another query to get data UserOrderArriveAddress::select('order_id','addresse_id');

ethar's avatar
ethar's avatar CharizardLaravel
1

Query not giving expected result in Laravel

In my Laravel-5.8, I have this Query: From the query above, I want to display all the employees, either the record matches or not. Then, compare the matching records. The problem now is that whenever there is no matching record for work_location_id for any employee as in: ->join('hr_work_locations AS l', function($join) use ($userCompany) { $join->on('l.id', '=',

noblemfd's avatar
noblemfd's avatar tovisbrats...Eloquent
3

laravel api Resource extra query

i have table category and table category_info which has translation of category. and in category model i have a relation : translation_category_info which get the translation with current locale . i loaded the relationship category.translation_category_info . in api resource i want to display the title of translation_category_info like this : 'title' => $this->translati

TarikAli's avatar
TarikAli's avatar TarikAliLaravel
0

does laravel disconnect to database after each query?

I check on DB by command: show full processlist and see some sleep process. I read some blog and they said laravel and php always disconect DB after finish query. But in my DB still have some long time sleep process . https://gyazo.com/38c4c3765b00a1a87455e4c57f35e782 I know that I can set wait_timeout to reduce the sleep connection. But my question is : Why laravel doesn't di

mimi0110's avatar
mimi0110's avatar sr57Laravel
14

How do I create such a query in DB Query Builder?

How do I create such a query in DB Query Builder? SELECT * FROM table WHERE left(Colum, 5) LIKE '12345'; Thanks for Help ?

Nosean's avatar
Nosean's avatar a4ashrafLaravel
3

query as a function inside model for reusing

Hi Everyone, I wanted to use a common query in multiple controllers, so I decided to put it in a model as function. But initially I used as simple function, but it didn't work as it doesn't work without creating object in controller. Then I used scope like below: \\Model - Section public function scopeAllSections() { return $this->get()->pluck('sname', íd'); } \\Contr

ramniksingh's avatar
ramniksingh's avatar SilenceBri...General
8

SQL - WHERE IF condition in QUERY BUILDER request

Hello everyone, Since many days I'm trying to make a complex request to filter some values but without success. I'm here now to find some help and if you have a some answers for me :) I have a table "user_working_locations" to store default locations for each day of the week and also to store a custom location for a specific date as you can see below : id user_id

Binho's avatar
Binho's avatar rodrigo.pe...Eloquent
21

Error in query but same is working in phpmyadmin query

My laravel code is as below $portfolio = Portfolio::selectRaw('portfolios.id,portfolios.quantity,(SELECT SUM(pnls.quantity) GROUP BY pnls.portfolio_id) as pnlotal') ->leftJoin('pnls','pnls.portfolio_id','portfolios.id') ->groupBy('portfolios.id') ->get(); The above code giving error as SQLSTATE[42000]: Syntax error or access violation: 1055 'maindb.portfolios

arctushar's avatar
arctushar's avatar arctusharGeneral
8

How to scope the orWhere query to the original relationship w/o manually writing it

The union query works great & is correct but seems quite slower. $first = auth()->user() ->notifications() ->where('data->matter_id', 7); $notifications = auth()->user() ->notifications() ->whereJsonContains('da

localpathcomp's avatar
localpathcomp's avatar trinEloquent
1

Adding query results to view

I have an EntityController and the index method gets all the Entities and sends them to the view where I display them in a table. I have then created a basic SQL query to get a sum of all the Bills (one to many relationship) for each Entity. How do I then add this or pass this across to the Index view in order to show the total amount of Bills for each Entity in my table? This

SarahS's avatar
SarahS's avatar SarahS74Laravel
2

Relationship Query

Hi, If i perform the following query I get the attributes from table 1. $data = Build::find($id)->get(); If I perform the following query I get the attributes from table 2. $data = Build::find($id)->website; I'm struggling to find the syntax to get the attributes from table 1 and table 2 at the same time. Thanks.

MoFish's avatar
MoFish's avatar MoFishLaravel
2

Understanding this code snippet, debug a $query

I have the task of debugging some code that isn't mine and quite frankly I have no clue what is happening in this code. The situation is that this code works for everyone else except a specific provider and I don't know why, and I need to find out what is happening. This is the method in my controller public function postData(Request $request) { $query = Product::qu

eggplantSword's avatar
eggplantSword's avatar msslgomezCode Review
2

Complex db query

Hi I have column Header as below item | price| quantity | buysell Now I want query result as below What is the sum of price*quantity based on Item based on buy What is the sum of price*quantity based on Item based on sell What is the sum of price*quantity based on item What is the sum of pricequantity of buy - sum of pricequantity of sell based on item is it possible? plz hel

arctushar's avatar
arctushar's avatar jlrdwEloquent
1

Date casting in query

I have a scope that applies to one of my models. It accepts a Carbon date instance that I am using to filter the results of the query. It is filtering on a date column, and when I pass the raw Carbon instance in to the where clause, it does not format the date correctly. I am expecting the format to be 'Y-m-d' and it is using 'Y-m-d H:m:s'. I have tried to cast the date fie

bradycharron's avatar
bradycharron's avatar bradycharr...Eloquent
2

Laravel query using whereHas

I have 2 models: car painted color, and cars public function color() { return $this->hasOne(Colors::class)->latest(); } I need to search the latest painted car color but with whereHas, it's searching all colors $colors = ['black', 'white']; $cars->whereHas('color', function($cars) use ($colors) { return $cars->whereIn('paint_color', $colors); }); How

imaxim287's avatar
imaxim287's avatar imaxim287Eloquent
15

Opposite of `select` in query builder?

Is there any method in query builder that behave opposite of select method ? I want to get every columns Except the given columns? $user->comments()->someMthodToGetEveryThingExceptTheseFields(['filed', 'field2'])->get();

untymage's avatar
untymage's avatar kimaLaravel
14

Where the good place to write the logic of convert GET request query before controller method

a GET request with query string http://localhost/users?regions[]=1&regions[]=1&regions[]=2&regions[]= array:4 [▼ 0 => "1" 1 => "1" 2 => "2" 3 => null ] Controller public function index(Request $request) { $users = User::select( 'id', 'name', ) ... ... ... many filter ... .

newbie360's avatar
newbie360's avatar newbie360Eloquent
3

Mysql Query using laravel

They are two tables : table ' a' and table 'b' and table 'b' have foreign key of table 'a'. 'a' as users table and 'b' as habits table. for one users they are 5 habits column in habits table. Now without writing query on habits table..i want to delete the habits all columns.

yadhul's avatar
yadhul's avatar yadhulLaravel
6

How to query the db and return a count of records for each month

I am trying to query the count of tickets in our support crud for each month but i am not sure how to get this done. Can anyone help. Its for a chart on a dashboard. Here are a couple that i use to get total count but no idea how to break them up into months. $returns = Support::where('case_reason_id',1)->get(); $troubleshooting = Support::where('case_reason_id',2)->g

madsynn's avatar
madsynn's avatar guybrush_t...Eloquent
13

Is there a shorter way to do this query?

Just curious if there are any other ways to get the same result for this query. $categories = ProductCategory::whereHas('products', function($query) use($request) { $query->whereNotIn('type', ['service', 'misc']); })->published()->get(); Or This query : $supports = Support::groupBy('support_status_id', '!=', 4 )->havingRaw('COUNT(*) > 1')->get(); Scope q

madsynn's avatar
madsynn's avatar guybrush_t...Eloquent
5

need help for creating a complex query (orders, users)

I have two tables orders and users. I want to create a query builder to get an array like this: [ ['first_name' = 'Test', 'last_name' = 'User', 'number_of_orders' = 16, 'orders_value' = 7580], ... ] the first 10 entries order by 'orders_value' desc. I have made it work like taking all the users (there are over 1000 users) and itereate each user in orders to calc the sum

calin.ionut's avatar
calin.ionut's avatar calin.ionu...Eloquent
1

Bulk Update based on field in query field condition

Hi everyone. I have alot of data (50 M) to update based on some conditions and eventually a Task Scheduler will do the same job every 1 hour for the new incoming records so I preferably don't want to use a for loop because this will create a query for every record I've read that for bulk update, the suggested method is using the Query Builder so i've managed to do this line of

Alexb0903's avatar
Alexb0903's avatar mabdullahs...Laravel
2

Query Builder exact match first and then try loose matches PostgreSQL

I am using PostgreSQL. Situation: Say I have quite a few posts with "Shoes" in the title but there is also a post where the title is only the word "Shoes". Aim; I want to perform a database query where it shows the post with the exact match of "Shoes" first, and then all other results where the word "Shoes" can be anywhere within the titl

CLab's avatar
CLab's avatar CLabLaravel
4

Query Builder - Searching records by a many-to-many relationshop

Hi All, I have 2 models, kids and guardians linked by a many to many relationship. I need to retrieve all records from the kids table that are related to any id in a given array of guardians. I'm currently using the query below (which works, it just seems rather janky). Does anybody have suggestions to improve it? if($request->filled('guardians')) { $guardians = $request-&g

Stucks's avatar
Stucks's avatar bugsyshaEloquent
3

Stuck on query age range

can anyone help for check the logic of scopeOfAgeRange is correct, Thx Controller class SearchStaff extends Controller { public function __invoke(Request $request) { $staffs = Staff::ofFilter($request)->get(); return view('staffs.index', compact('staffs')); } } Model class Staff extends Model { public function scopeOfFilter($query, $request)

newbie360's avatar
newbie360's avatar newbie360Laravel
6

Building a query

Hello Tell me how to parse an array into lines, I get a lot of information on the API I need. Sample code if ($result['status'] === 'success') { var_dump($result['callDetails']); } result: array(17) { ["companyID"]=> string(4) "17111" ["generalCallID"]=> string(10) "1234141" ["callID"]=> string(10) &q

HoofBeats's avatar
HoofBeats's avatar bugsyshaLaravel
1

increase multiple columns in one query

Hi，i wanna to increase multiple column value in one query. below will do the trick $data = model::find(24); // $data->Update([ // "col_1" => \DB::raw('col_1 + ' .$amount), // "col_2" => \DB::raw('col_2 + ' .$amount), // "col_3" => \DB::raw('col_3 + ' .$amount),

andiliang's avatar
andiliang's avatar andiliangEloquent
4

Eloquent query relation based on another relation's column value

Hello, Considering the following four tables language [id,name] article [id] article_translation [id,article_id,language_id,title] image [id,article_id,language_id,title] And given the following values language [1,english],[2,spanish] article [1] article_translation [1,1,1,"article title"] image [1,1,1,"pic 1 description"],[2,1,2,"pic 1 description&quo

tlacaelelrl's avatar
tlacaelelrl's avatar tlacaelelr...Eloquent
2

Query Builder problem with 'where'

Hi, I am having a problem. I have a very long query with multiple joins. At the end of my query builder I have this ->whereIn('ad.ad_type', $adType) ->orWhereIn('ad.ad_one_id', $liveAdOne) ->orWhereIn('ad.ad_two_id', $liveAdTwo) ->orWhere('ad.status', '=', 'active') I have 2 problems: The ->orWhe

User1980's avatar
User1980's avatar User1980Eloquent
7

Creating a non-clashing appointments booking query…

I’m hoping someone can advise the best way of handling clashing appointments in a salon system I’m looking to build. In its most basic form, the admin will add staff members and treatments to the system (see below tables - I’m ignoring other columns right now, just the ones I think I need). When the customer goes to the site, they’ll choose the treatment and date. I’m thinking

idcreatv's avatar
idcreatv's avatar idcreatvCode Review
2

Laravel Query with multiple where clause

In my Laravel-5.8, I have this query: $companies = $this->getCompanies(); $company_credentials = OrgSocialiteSetting::where('id', $companies->id)->first(); $azureUser = Socialite::with('azure')->user(); $user = User::where('company_id', $company_credentials->company_id)->where('email', $azureUser->email)->orWhere('username', $azureUser->user['mailNi

noblemfd's avatar
noblemfd's avatar MichalOrav...Laravel
4

listen and get all query logs in app

Where should i putDB::enableQueryLog and DB::enableQueryLog` in my app to fetch all query logs ?

untymage's avatar
untymage's avatar tykusLaravel
4

Build Eloquent query with child aggregate

Hello All Is it possible to build an eloquent query such that I get the parent, and then children as a sum aggregate. so, Order: 1 2 3 OrderLines: type value A 10 A 12 B 10 Result : $order[0]->orderlines[0]->type would equate to A $order[0]->orderlines[0]->value would equate to 22 $order[0]->orderlines[1]->type would equate to B $order[0]->orderli

osieman's avatar
osieman's avatar osiemanLaravel
9