Query Optimize Laravel (inRandomOrder, whereHas, search)

because my data is getting bigger． I have 3 queries that I want to optimize． the posts table currently has nearly 1 million rows． I use redis so I don't talk about query cache problem． project info: laravel 8．x, mysql5．7, php7．4．x 1．I want to get 8 post related (by category) order random in post detail page． index: posts．status, post_category．post_id, post_category．category_id

lakazai's avatar
lakazai's avatar lakazaiLaravel
3

Optimising query (loop inside loop)

Is there a better way to optimise 2 loops running, one inside another. I have a list of categories and each category has bunch of topics. I'm pulling all categories belonging to given parent element (subject) and all topics that have relationship to a category. Inside my view I'm running a loop of categories and inside it I have another foreach loop with if statement checking i

redlik's avatar
redlik's avatar redlikEloquent
8

Disable CastAttribute when performing a query ?

I have a column which accepts a CastAttribute , How can i temporary disable casting when i perform a query ? I mean: $post->comments()->pluck('level'); the raw property in my table have integer value but i casted it to string, How can tell laravel to ingore casting for level property in this query ?

untymage's avatar
untymage's avatar tykusLaravel
6

Help me query to show a family tree?

I have referencing a family data model: People: (id, name, email) Family: (id, head_1_id, head_2_id) Relation: (id, people_id, related_id, relation_type) people_id is fk with People, related_id is fk with People, relation_type: Son, Father, Mother,... Parent: (parent_id, people_id, parent_id, parent_type) parent_id fk with People and pk Why need table Parent, And how to query t

vantinhmmo's avatar
vantinhmmo's avatar jlrdwLaravel
1

Query Builder - Optimizing the repeated portion of the 3 queries below?

Hi all, I have to run 3 queries, which shares a similar 'base of the query' and only differs in the final few whereBetween condition, is there a way that I can 'group up' the earlier similar parts of the query, so that I can reuse it, and potentially reduce mistakes along the way, as I'll only need to change the 'base of the query once' and have it applied to the subsequent que

TTSB's avatar
TTSB's avatar TTSBLaravel
10

How do I avoid duplicate query while using relationship on self model?

I'm trying to create 1-level nested comment system. That is, each comment can have one reply. My db structure is like this - id | domain_id | user_id | channel_id | parent_id | title | body The parent_id is meant to reference the id column on the same table; or it can be NULL. My model is as follows: class Post extends Model { public function user() { $this->belongsTo(

thebigk's avatar
thebigk's avatar thebigkEloquent
7

How to Query Relationship in Complex Raw DB using Laravel

I have this models in Laravel-5.8: class Employee extends Model { public $timestamps = false; protected $table = 'employees'; protected $primaryKey = 'id'; protected $fillable = [ 'id', 'first_name', 'last_name', 'hr_status', 'employee_type_id', ]; pub

noblemfd's avatar
noblemfd's avatar Tray2Laravel
5

Retrieve an input form the query string

I am trying to get ?limit=50 from my query string reading here: https://laravel.com/docs/8.x/requests#retrieving-input my api path: api/todos goes here: App\Http\Controllers\TodoController@index index method in TodoController public function index() { // $todos = $this->user->todos()->get(['id', 'title', 'body', 'completed', 'created_by']);

boyjarv's avatar
boyjarv's avatar SnapeyLaravel
8

Query Builder quandary in livewire component

Problem: how to get results from a query and then search only those results I have a treatments table but want to display only the treatments assigned to a particular dentist. I am using mount() to bring in the logged in dentist's user ID like this: public function mount() { $this->dentist_id = Auth::id(); } I verified that the ID is being passed successfully. This work

jimb814's avatar
jimb814's avatar jimb814Livewire
3

Is there a better way to do this query for performance?

Hi Devs, I have a controller that returns products with images controller $this->products = Cache::remember('topSelling', now()->addMinutes(30), function() { return Product::inRandomOrder()->select(['name', 'slug', 'price', 'on_sale', 'is_new', 'id'])->with('photos:product_id,id,images')->take(8)->get(); and then in my view @foreach($products as $p) {{ $p

Wakanda's avatar
Wakanda's avatar MichalOrav...Laravel
6

Laravel RESTful api - best practice for query params

I have a simple website running Laravel Jetstream with Teams enabled. On this website, you can create different "to-do tasks", which are owned by a team. I have a model called Task. I am trying to create a public facing API, so my users can query their tasks from their own applications. In my routes/api.php file, I have added this: Route::middleware('auth:sanctum')-&g

oliverbusk's avatar
oliverbusk's avatar martinbeanLaravel
3

Query To Shows Data on Date Range

I have database table form like this: |ID|Vehicle|Date_start|Date_end|Status| |1|A|2021-01-17 12:00:00|2021-01-18 13:00:00|Finish| |2|B|2021-01-19 12:00:00|2021-01-21 12:00:00|In Use| Then, i have code like this: $datestart = '2021-01-19 '.Date("H:i"); $dateend = '2021-01-20 '.Date("H:i"); $cek =Peminjamanrand

Dikurr11's avatar
Dikurr11's avatar Dikurr11Laravel
6

Laravel pivot insert creating invalid query

using below code: $vehicle_type = $request->vehicle_type; $vtype =array('vehicle_type'=> $vehicle_type); $vehicletype = VehicleType::create($vtype); $mileage_slot = $request->input('mileage_slot', []); $fare = $request->input('price', []); for ($i=0; $i < count($mileage_slot); $i++) { if ($mileage_slot[$i

muazzamazaz's avatar
muazzamazaz's avatar muazzamaza...Laravel
6

Query builder and multiple queries advice needed

Hello there! I'm making my query builder, code runs and works perfectly but I need an advice. Here's my code: // method for updating a single record in database in query builder public function update($table, $values, $filter) { $statement = $this->pdo->prepare("update {$table} set " . implode('=?, ', array_keys($values)) . "=? " .

c4ost's avatar
c4ost's avatar jlrdwGeneral
1

Retrieving multi-table data through the query builder

I'm running a query with a join in it, such that when there are two conflicting columns (for example: articles.status and authors.status) only one of the columns is output (and it's not possible to tell which). The complication is that I'm building a filtering system that constructs the query dynamically (so it's unknown what tables and columns will be involved, and the conflic

rchaffer's avatar
rchaffer's avatar rchafferLaravel
1

Constrain query by relation's timezone?

Hi there! I have an Event model, a Customer model, and Location model. Events have a customer_id and a location_id. Locations have a customer_id and a timezone. Currently, I’m grabbing all events with their location relation, then only after getting those, filtering down the collection of events by the event start date translated into the location’s timezone. Looks basically li

JoshP's avatar
JoshP's avatar JoshPEloquent
2

Querybuilder executes the query twice

I have a code block that returns district name of a real estate listing but after I got the first record and try to get the name attribute of the record Laravel executes the query twice. $district = DB::table('locations') ->whereRaw('st_within((select geom::geometry from listings where id = ' . $this->id . ' )::geometry,locations.geom::geometry) and admin_leve

cankansu's avatar
cankansu's avatar SnapeyEloquent
10

livewire query pagination scope

Is there a best practise for applying global query scopes from within a Livewirecomponent? Since there is no request going through the middleware enforcing the scope this gets ignored when accessing the paginated pages Normally all livewire requests are passed through the web middleware but on specific routes i would like the livewire render requests to go through additional mi

jorgb's avatar
jorgb's avatar jorgbLivewire
1

No query results for model

I'm trying to update my product but I'm getting this error "message": "No query results for model [Modules\Products\Models\Product] 488" Here is my vue file <template> <div> <div class="row"> <div class="col-sm-6"> <div class="form-group">

Ifrit's avatar
Ifrit's avatar IfritLaravel
0

Query building with Eloquent Models

How do I build the below query with Eloquent Models? select users.*, posts.* from users, posts where users.id = 1 and users.id = posts.user_id and posts.status = 'draft'

lonelearner's avatar
lonelearner's avatar lonelearne...Eloquent
7

Redirect returning query param instead of path

Laravel 8 Intertia.js Within my UserController::store() endpoint, I create a user and want to be redirected back to the newly created resource, /users/{id}. According to the Laravel documentation, I can use the following: return redirect()->route('users', ['id' => $user->id]); or return redirect()->route('users', [$user]); The issue is that the redirect is return

tuesdave's avatar
tuesdave's avatar tuesdaveLaravel
3

Is it possible to see raw sql query?

Bill::with(['user', 'project'])->get(); Is it possible to see raw sql query of this?

cola's avatar
cola's avatar Tray2Laravel
3

Query Builder returning the wrong data

I expect this code to return just 3 results (the 3 unpublished posts) but it returns everything in the db::table. protected function draft() { return $this->builder->wherePublished(false); } protected function q($q) { return $this->builder->where('title', 'LIKE', '%' . $q . '%') ->orWhere('body', 'LIKE', '

successdav's avatar
successdav's avatar newbie360Laravel
3

Replacing /n in query

I have a query that looks like this $query = DB::select( " SELECT p.id, p.name, regexp_replace('/\s\s+/', ' ', p.description) as description, FROM public.products p ORDER BY sort ASC " ) but when I run it my output has replaced my description with /\s\s+/. What I need is for my query to replace /n w

Ifrit's avatar
Ifrit's avatar sr57Laravel
11

Query with where() and orWhere() ... "and where" ?

Hello, I want that the three conditions are evaluated together. ->when($this->filtre_statut_mev != 0, function ($query) { $query->where('mevs.statut', $this->filtre_statut_mev); }) ->when($this->filtre_statut_vente != '', function ($query) { $query->where('ventes.statut', $this->filtre_s

vincent15000's avatar
vincent15000's avatar vincent150...Eloquent
2

Modify query within a variable

How can i modify a variable when it have query builder ? i mean: $post = Post::where('id', 1); if(myCondition){ $post->first(); } if(anotherCondition){ $post->where('body', 'example'); } dd($post); $post always return query builder instance, How can i avoid this : $post = Post::where('id', 1); if(myCondition){ $post = $post->first(); } if(anotherCon

untymage's avatar
untymage's avatar cheemojipl...Laravel
19

Stumped on how to do a simple query off GET variables

Hi everyone, I have a very simple property search form and I want to simply query my listing model based on the get variables. The problem I'm having is when one of the GET variables isn't used (such as me putting in a min price and not a max price). What is the proper way to search just by the variables in GET? public function index(Request $request) { if($requ

ChrisF79's avatar
ChrisF79's avatar sr57General
2

Search query on ->when() with relationship (Solved)

Hi. I'm just trying to get a search query back from 2 dropdowns, one is a relationship of the Module model. Im using the '->when()' property to get the results if the query is passed through, but i can't figure out how to get it to work on the relationship. $modules = Module:: when($this->org != '', function ($query) { return $query->where('organi

skoobi's avatar
skoobi's avatar sr57Livewire
2

Translating SQL into Query Builder syntax

I am struggling to grasp how to write these kinds of queries in Laravel. This works fine: SELECT * FROM `messages` JOIN `conversations` ON messages.conversation_id = conversations.id WHERE sent_to = 6 AND created_by = 3 OR created_by = 6 AND sent_to = 3; This returns nothing: public function getConversationBetween($user_id, $auth_id) { return DB::table('conversations')

jimb814's avatar
jimb814's avatar jimb814General
2

Repetitive eloquent query across 1 controller.

Let's assume that I have a controller which is dedicated to control the views, so i have different types of views and i send to them different variables, but - i have one query which repeats itself across all the functions, in this case its $companies = Company::where('enabled',1)->get()->sortByDesc('score'); What would be the right approach to keep it DRY? Is it safe to

davidleads's avatar
davidleads's avatar MichalOrav...Laravel
4

How to query the database using a different field other than id

Hi devs, I am trying to query a database using a custom field see code below if (!empty(auth()->user()->referred_by)) { //get referrer user id $referrer = User::where('affiliate_id', auth()->user()->referred_by)->get(); dd($referrer); Earning::create([ 'user_i

Wakanda's avatar
Wakanda's avatar piljac1Laravel
1

Laravel query with 'LIKE'

Hello Guys, So I've created a live search and have the following rows in my table id, application, name, c_id & o_id. I'm having an issue where I need it to search the table and bring back only the matches from the application value, but instead it's searching everything due to the "Orwhere" statements. I can't find an "AndWHERE". Here's my current query

dk4210's avatar
dk4210's avatar dk4210Laravel
10

How to do a query in Laravel

I have been unable to find an answer in the Laravel docs. I am using Laravel 8. I have a conversations table with two fields that I want to check: sender_id and receiver_id. Table: Schema::create('conversations', function (Blueprint $table) { $table->id(); $table->integer('sender_id'); $table->integer('receiver_id'); $tab

jimb814's avatar
jimb814's avatar jimb814Laravel
8

Eloquent Query Doesn't work

Hi, I'm modifying my like system a bit, everything works but the query to insert the notification about the like just put, doesn't work. I can't understand why! This is my code: function save_like(Request $request){ $item = Items::findOrFail($request->item); if(Auth::user()->hasLiked($item)) { Auth::user()->unlike($it

Marcolino922's avatar
Marcolino922's avatar Marcolino9...Laravel
5

Query Builder Table Alias

I want to run this SQL (simplified than the real case): UPDATE mytable mytbl SET col = (SELECT id FROM othertable WHERE mytbl.aaa = bbb) tried this using query builder DB::table('mytable', 'mytbl') ->update([ 'col' => DB::table('othertable')->select('id')->whereRaw('mytbl.aaa = bbb')->first() ]) got QueryException: Column not found: 1054 Unknown column 'mytb

coder222's avatar
coder222's avatar coder222Eloquent
7

Joining tables in a query

Hi, I currently have a comments table and a user table. I would like to join the 2 tables up and get the 'id', 'users_id','username','post_id','content','created_at', 'updated_at'. But I also don't want to join the entire user table, maybe just add the username and email column. I get the error: A subquery must be a query builder instance, a Closure, or a string. User migration

jkwok678's avatar
jkwok678's avatar tykusEloquent
1

Append a query to model

I have a table named products that has 2 columns named (id, name) and listed the table in view using this query: $rows = Product::paginate(); I want to append remaining quantity to rows query like this. DB::select("SELECT IFNULL(q.bought, 0) - IFNULL(q.sold, 0) AS remain FROM products LEFT JOIN (SELECT buy_details.product_id, SUM(buy_details.quantity) AS bought, SUM(sell_

Armani's avatar
Armani's avatar ArmaniEloquent
1

Getting my query to get the total amount

I have a couple of projects and each project has a block and an activity. What I'm trying to do is list all the blocks out but also get the total price of those blocks and the total planned value of those blocks and the way I've done this is that the planned values and the pricing is in 2 different tables The problem is, is that I'm getting the totals for individual projects, b

Shiva's avatar
Shiva's avatar Tray2Laravel
3

Query not execute php

nothing is sent to the database, what could I solve for it? global $wpdb; $order = new WC_Order( $order_id ); $items = $order->get_items(); $product_id = 193; // that's a specific product ID $query = $wpdb->insert('wp_pms_member_subscriptions', array( 'id' => 4, 'user_id' => 12, 'subscribtion_plan_id' => 233, 'start_

KikoLdasd's avatar
KikoLdasd's avatar Tray2General
9

Problems finding last element of Query Builder cursor()

Hello! I'm working on an export code and we decided to go with cursor() because we are exporting a massive ammount of data. The problem is that I want to implement some kind of progress report, and working with cursor I only have access to one item at a time in memory, not knowing the total ammount of rows I am exporting makes it dificult. I have a Query Builder like this: $que

fernandogross's avatar
fernandogross's avatar fernandogr...Laravel
13

Query WhereYear() WhereInYear()

in my query I am able to do whereYear() but i need something like whereInYear() how would you do it?

extjac's avatar
extjac's avatar extjacGeneral
3

Query a relationsship

I am trying to query a releationship like this: $first = Projectvoucheritem::select('*','rentalstop AS tdate') ->with('transmissionbegin') ->with('transmissionend') ->with('transmissiondrivers.users') ->whereNotNull('rentalstop') ->whereDate('rentalstop', '>=', $start) ->whereDa

maltekiefer's avatar
maltekiefer's avatar maltekiefe...Eloquent
3

Extend Query Builder Upsert()

I would like to write my own upsert() method for the query builder. I am not using any models or eloquent. The upsert() method is found here: \Illuminate\Database\Query\Builder How would I go about doing this? Where should I extend it and how can I then use it?

aaronbains's avatar
aaronbains's avatar aaronbainsLaravel
5

Looking for assistence with simple change of SQL query

You need to know, these two tables are connected by ORDER_ID: //TABLE order_products id | order_id //TABLE order_deliverydates id| order_id | calculated I want sort this query by CALCULATED: SELECT op.id, ........... os.shipping, swe.note AS custom_note FROM order_products op LEFT JOIN order_shipping os ON os.batch_id = op.batch_i

hiandy's avatar
hiandy's avatar Tray2Code Review
5

Query for add multiples values for different data

Hi, how can I add for example multiple data in the database, for hours every 10 minutes for certain data of the month? For example I have monday, sunday, thursday 13:00 - 16:00 and an interval of 10 minutes. I have 6 values ​​in one hour and 18 in 3 hours. How can I write a fixed query for those days of the month and put an interval of 10 minutes?

KikoLdasd's avatar
KikoLdasd's avatar SnapeyGeneral
1

How to query the database when user logs in

I am new to Laravel. I have two types of users. I have a messages table that I want to query for unread messages when the user logs in. I like the login functionality Laravel provides out-of-the-box, but I do not know where to put a query, or if this is even a good idea because there are better ways to handle it. If anyone can help point me in the right direction, I would appr

jimb814's avatar
jimb814's avatar jimb814General
8

Ajax call returning null result on query.

hey everyone. I have a table charters where there is a column charter_code . now i want when the form is submitted it should first check the charter_code inside charters table , whether it is available or not. here is my controller public function getCharterCode(Request $request) { // dd($request->all()); $charter_code = $request->code; $query =

maaz's avatar
maaz's avatar maazLaravel
3

Select from a Subquery using Query Builder

I have a data table that I want to allow users to sort by whichever column they choose. One of these data tables is supposed to show the 20 most expensive parts in a workorder. Here is the query I'm trying to duplicate using Query Builder: select PART_NUMBER, DESCRIPTION, UNIT_PRICE from ( select top 20 p.ID as PART_NUMBER, p.DESCRIPTION, ps.UNIT_PRICE from requirement r

grozavule's avatar
grozavule's avatar MichalOrav...Laravel
2

How to apply pagination in different query results ?

How to apply pagination in different query results ? ->join('sectors', 'sectors.id','incidents.sector_id') ->join('incident_notes', 'incident_notes.incident_id','incidents.id') ->leftJoin('bdms', 'bdms.id','sectors.bdm_id')

jinsonjose's avatar
jinsonjose's avatar MichalOrav...Laravel
2

Combine query with collection.

I have created filters to search based on the input. For example, if the request has 'number', it uses the Number class, which implements Filter. The second thing I have is a search where the response is returned through a JsonResource. I'm using this in a Vue component. I want to combine the two. Where inside of the filter do I implement the JsonResource? namespace App\Order

ajsmith_codes's avatar
ajsmith_codes's avatar ajsmith_co...Laravel
7