Laravel – Joining Model with Table Laravel 5.2

eloquentjoin;laravellaravel-5.1laravel-5.2

I have created a model in Laravel.

class deals extends Model
{
//
protected $table = 'tbl_deal';
}

Now I want to use this model to retrieve data in my Controller. However, the data extracted from this model also needs to be constrained by a column in another table. Below is the basic schema

tbl_deal

deal_id
merchant_id
deal_text

tbl_merchant

merchant_id
merchant_url_text

My current Controller has the following code to extract all deals from deals model.

$deals = deals::all();

I want to constraint the deals by merchant_url_text. Since that is in merchant table, I would need to join it with the existing deal model.

Any help on how I can do that in my controller and if this is the right way to solve this kind of problem.

Thanks.

Best Answer

Add to your deals model

the following function (called relationship):

public function merchant()
{
   return $this->belongsTo(Merchant::class, merchant_id);
}

and now you will be able to get all deals with merchant using

$deals = deals::with('merchant')->get();

For example to display the first one use:

$deal = $deals->first();
echo $deal->merchant->merchant_url_text;

However to use this you need to create also Merchant model and using this method no join will be used.

In case you want to use simple join instead of relationship in this case, you can use:

$deals = deals::selectRaw('tbl_deal.*, tbl_merchant.merchant_url_text')
              ->leftJoin('tbl_merchant','tbl_deal.merchant_id','=','table_merchant.merchant_id')->get();

And now to display 1st merchant text you can use:

$deal = $deals->first();
echo $deal->merchant_url_text;

I strongly recommend to read Laravel documentation to fully understand basic Laravel concepts before writing any code (for example by convention model name should be Deal instead of deals and table name should be deals instead of tbl_deal, similar for primary key - it should be simple id instead of deal_id).

MS SQL

UPDATE
    Sales_Import
SET
    Sales_Import.AccountNumber = RAN.AccountNumber
FROM
    Sales_Import SI
INNER JOIN
    RetrieveAccountNumber RAN
ON 
    SI.LeadID = RAN.LeadID;

MySQL and MariaDB

UPDATE
    Sales_Import SI,
    RetrieveAccountNumber RAN
SET
    SI.AccountNumber = RAN.AccountNumber
WHERE
    SI.LeadID = RAN.LeadID;

Sql-server – Update a table using JOIN in SQL Server

You don't quite have SQL Server's proprietary UPDATE FROM syntax down. Also not sure why you needed to join on the CommonField and also filter on it afterward. Try this:

UPDATE t1
  SET t1.CalculatedColumn = t2.[Calculated Column]
  FROM dbo.Table1 AS t1
  INNER JOIN dbo.Table2 AS t2
  ON t1.CommonField = t2.[Common Field]
  WHERE t1.BatchNo = '110';

If you're doing something really silly - like constantly trying to set the value of one column to the aggregate of another column (which violates the principle of avoiding storing redundant data), you can use a CTE (common table expression) - see here and here for more details:

;WITH t2 AS
(
  SELECT [key], CalculatedColumn = SUM(some_column)
    FROM dbo.table2
    GROUP BY [key]
)
UPDATE t1
  SET t1.CalculatedColumn = t2.CalculatedColumn
  FROM dbo.table1 AS t1
  INNER JOIN t2
  ON t1.[key] = t2.[key];

The reason this is really silly, is that you're going to have to re-run this entire update every single time any row in table2 changes. A SUM is something you can always calculate at runtime and, in doing so, never have to worry that the result is stale.

Best Answer

Related Solutions

SQL update from one Table to another based on a ID match

MS SQL

MySQL and MariaDB

Sql-server – Update a table using JOIN in SQL Server

Related Topic