Category : join

I have four tables default_products_product_mileage_gap default_products_mileage_gap default_products_products default_products_products_mileage_gaps I am trying to update a column number_of_products_sold to some value using laravel What I HAVE TRIED IS $qty=1; $m= DB::table(‘products_products’) ->join(‘products_products_mileage_gaps’,’products_products.id’,’=’,’products_products_mileage_gaps.entry_id’) ->join(‘products_product_mileage_gap’,’products_products_mileage_gaps.related_id’,’=’,’products_product_mileage_gap.id’) ->join(‘products_mileage_gap’,’products_mileage_gap.id’,’=’,’products_product_mileage_gap.mileage_gap_id’) ->where(‘products_product_mileage_gap.number_of_products’, ‘>’, 0) ->where(‘products_mileage_gap.name’,’=’,$mileage_name) ->where(‘products_products.id’,’=’,$id) ->update(array(‘products_product_mileage_gap.number_of_products_sold’=>$qty)); Here number_of_products_sold is not updating. How to update the column Sourc..

Read more

I am new to pyrocms, I want to join two tables in pyrocms. My two tables are milege and products. How to join this two table in pyrocms What I have tried is: {% set table_data=query().from(‘milege).join(‘products’).on(‘milege.id’,’=’,’product.mid’).where(‘user_id’,’1′).get() %} I am getting errors on executing this query. How to join these two table data in pyrocms. Source: ..

Read more

DataTables warning: table id=ajaxDatatable – Requested unknown parameter ‘addresses’ for row 0, column 1. For your reference, I’m using "https://datatables.net/examples/data_sources/server_side" this jquery datatable. There is a one to many relationship between a company and address (e.g. One company has many addresses). Migration: Companies { Schema::create(‘companies’, function (Blueprint $table) { $table->id(); $table->string(‘name’); $table->timestamps(); }); } Migration: ..

Read more

I have three tables: super_consumers , balance_ins, recharges super_consumers table: id name ———- —- 1 A 2 B balance_ins table: id super_consumer_id amount type(1,2) — —————– —— —- 1 1 10 1 2 1 20 1 3 2 10 2 recharges table: id super_consumer_id amount status(0,1) — —————– —— —— 1 1 5 1 2 ..

Read more

// how should i join these 4 tables users, pages, category, products using relationships intead in controller. //I also prefer calling these functions from corresponding models using respective function names but not database column names in view. Thanks in advance. These are my models //models class User extends Authenticatable{ use HasFactory, Notifiable; protected $fillable = ..

Read more

Hi respected community members, I am trying a query to fetch records from two tables and order by the latest record but still no luck. Basically, I have two tables. one is donations and second one is recurring_donation_invoices.donations table have a field type which can be onetime or recurring.if the donation is of type onetime ..

Read more

can anyone help me with Laravel join with count I’m trying to get the laravel to join with a count of three tables where table 1 one is a relationship with table 2 and table 3 Which display data of table1 and table and the count value of table three only with a relationship with ..

Read more

I have a query let’s take a look, DB::table(‘states’) ->join(‘brands’,’brands.state_id’,’=’,’states.state_id’) ->join(‘cities’,’cities.state_id’,’=’,’states.state_id’) ->select( ‘state_name’, DB::raw("(COUNT(‘cities.city_id’)) as cities"), DB::raw("count(‘brands.brand_id’) as brands")) ->groupBy(‘state_name’) ->get(); I want to get the state_name from the states table & counts the cities and brands under the state. But in these queries, the problem is city counts & brand counts show the same ..

Read more

I have a query let’s take a look, DB::table(‘states’) ->join(‘brands’, ‘brands.state_id’, ‘=’, ‘states.state_id’) ->join(‘cities’, ‘cities.state_id’, ‘=’, ‘states.state_id’) ->select( ‘state_name’, DB::raw("(COUNT(‘cities.city_id’)) as cities"), DB::raw("count(‘brands.brand_id’) as brands") ) ->groupBy(‘state_name’) ->get(); I want to get the state_name from the states table & count the cities and brands under the state. But in these queries, the problem is city ..

Read more

I have a table tracking which contains id name trackingNumber 1 John Doe 1Z21343V43DF4434 2 John Doe 1Z21343V43DF4435 3 Jane Smith 1Z21343V43DF4436 4 Mark Smith 1Z21343V43DF4436 and a table orders which contains id OrderNumber Name 1 12-12324232 John Doe 2 12-12324234 Mark Jacobs 3 12-12324236 Michael Smith when I run the query select * from ..

Read more

I have products, warehouses, transactions and transaction_details table. $p = Product::leftJoin(‘transaction_details’, ‘products.id’, ‘=’ ,’transaction_details.product_id’) ->leftJoin(‘transactions’, ‘transaction_details.transaction_id’, ‘=’, ‘transactions.id’) ->leftJoin(‘keep_details’, ‘products.id’, ‘=’, ‘keep_details.product_id’) ->selectRaw( ‘products.id,products.product_name productName, products.category_id, products.sub_category_id, IFNULL(sum(transaction_details.quantity), 0) as totalQty, products.package_qty, IFNULL(sum(keep_details.quantity), 0) as totalKeep’ ) ->groupBy(‘id’,’productName’,’category_id’, ‘sub_category_id’, ‘package_qty’) ->get(); return new StockCollection($p); By using the above query I am able to get all ..

Read more

I want to display my user score data together with the joint tables of user and organization. I also want to display the data using the User Score id. userScore Model: <?php namespace AppModels; use IlluminateDatabaseEloquentFactoriesHasFactory; use IlluminateDatabaseEloquentModel; class userScore extends Model { public $table = "user_scores"; use HasFactory; protected $guarded = []; public function ..

Read more

I have a code where Eloquent query builder joins the ManyToOne tables articles and users. The code looks like: $model = Article::with(‘user’)->select(‘articles.*’)); and then the model filters the user name $model = $model->join(‘users’, ‘articles.user_id’, ‘=’, ‘users.id’) ->where(‘users.name’, ‘like’, "%$value%"); This throws me an error: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: ‘users’ ..

Read more

i have two joined tables.. i need to sum all rows but when i use code DB::raw(‘sum(items.it_price_egp_in) As sum1’), i just get the selected value in items.it_price_egp_in not sum of all rows please check comment in code here is controller code $data = Item::select(‘items.it_photo_name’,’items.it_arabic_name’,’items.it_english_name’,’items.it_cod_1′,’items.id’,’items.shp_no_for_it’,’items.it_buy_date’,’items.it_qut_1′,’items.it_created_date’, ‘items.it_buy_date’,’items.it_storaged_date’,’items.it_out_of_stock’,’items.it_price_egp_in’,’items.it_other_fees_in’,’items.it_price_usd_out’,’items.it_supp_shipping_fees_out’,’items.it_other_fees_out’, ‘shipments.shp_destination_shipping_fees’,’shipments.shp_customs_fees’,’shipments.shp_permission_receive_fees’,’shipments.shp_storage_fees’,’shipments.shp_mid_fees’,’shipments.shp_other_fees_out’, ‘shipments.shp_clearance_fees’,’shipments.shp_trans_fees_out’,’shipments.shp_trans_fees_in’,’shipments.shp_other_fees_in’ , ‘shipments.shp_no’, // sum(items.it_price_egp_in) not working good just ..

Read more

There’s a table in my SQL DB called "projects" and it has a column in it "categories" which is a varchar(255) representation of a php array "[‘category_1’, ‘category_2’, ‘category_3′]" and what i’d like to do is put these categories into a separate table which would be made of a unique integer id in addition to ..

Read more

$users = DB::table(‘users’) ->join(‘course’, ‘users.id’, ‘=’, ‘course.courseId’) ->join(‘skills’, ‘users.id’, ‘=’, ‘skills.id’) ->join(‘subjects’, ‘users.id’, ‘=’, ‘subjects.id’) ->get(); dd($users); This gives me an empty result like this- items: []. I would like to join the 4 tables – users, course, skills & subjects and fetch all the datas in all the 4 tables. users table has the ..

Read more

I have StudentEducation model and in this model i got public function student_education_countries() { return $this->hasMany(StudentEducationCountry::class, ‘education_id’); } and public function student_education_education_types() { return $this->hasMany(StudentEducationEducationType::class, ‘education_id’); } in StudentEducationCountry model i got public function student_education() { return $this->belongsTo(StudentEducation::class, ‘education_id’); } public function student_country() { return $this->belongsTo(StudentCountry::class, ‘country_id’); } and in StudentEducationEducationType model i got public ..

Read more

i want to have all the records from district table even if there is no match in cases table in following query $object = DB::table(‘case_details as cd’) ->select( DB::RAW(‘SUM(cd.registered) AS registered’), "prv.$name AS province","crt.$name AS court", "dis.$name AS divan","c.court_id","c.divan_id" ); ->leftJoin(‘cases AS c’,’c.id’,’cd.case_id’) ->leftJoin(‘provinces as prv’,’prv.id’,’=’,’c.province_id’) ->leftJoin(‘courts as crt’,’crt.id’,’=’,’c.court_id’); ->rightJoin(‘districts as dis’,’dis.id’,’=’,’c.district_id’); $object = $object->groupBy("dis.id"); ..

Read more