SQL Query in Laravel QueryBuilder

Hey Guy's,

i have a problem with an SQL Statment in php. How can I rewrite this SQL query in Laravel Query Builder?

This is my Query before i use Laravel:

$sql = "SELECT khk.*, pft.*, tank.* from KHKAdressen khk, PFTankgasBestellungen pft, ECCTanks tank
		WHERE pft.Adresse = khk.Adresse
		and pft.Tank = tank.Tank		
		AND pft.Mandant = khk.Mandant
		and pft.Status = '$status'	
		order by case 
			khk.Auswertungskennzeichen when 'Tournus' then 0 else 1 end,
			pft.timestamp";

I don't know how to include multiple tables in the query This is my new Query in Laravel:

        $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')
                ->join('PF_Tank_gas_orders', 'PF_tank_gas_orders.Mandant', '=', 'KHKAdressen.Mandant')
                ->where('PF_tank_gas_orders.status', '=', $status)->get();

But i receive a Error Message:

SQLSTATE [42000]: [Microsoft] [ODBC Driver 13 for SQL Server] [SQL Server] The "PF_Tank_gas_orders" and "PF_tank_gas_orders" objects in the FROM clause have the same exposed name. Use dependent names to distinguish them from one another.

Can anyone help?

a4ashraf

5 years ago

Level 33

@nosean

Try this one may it work, but your PF_tank_gas_orders table is changed in your query, and your query builder code

	$orders = DB::connection('sqlsrv1')
            ->table('PFTankgasBestellungen as ptf')
            ->join('KHKAdressen as khk', 'pft.adress', '=', 'khk.Adresse')
            ->join('ECCTanks as tank', 'ptf.Tank', '=', 'tank.Tank')
            ->where('pft.Status', '=', $status)
            ->orderByRaw('case 
			 when khk.Auswertungskennzeichen = "Tournus" then 0 else 1 end,
			pft.timestamp')
			->get();

rodrigo.pedra

5 years ago

Best Answer

Level 56

Try this:


$orders = DB::connection('sqlsrv1')
    ->table('PFTankgasBestellungen')
    ->join('KHKAdressen', function ($join) {
        $join->on('PFTankgasBestellungen.Adresse', '=', 'KHKAdressen.Adresse');
        $join->on('PFTankgasBestellungen.Mandant', '=', 'KHKAdressen.Mandant');
    })
    ->join('ECCTanks', 'PFTankgasBestellungen.Tank', '=', 'ECCTanks.Tank')
    ->where('PFTankgasBestellungen.status', $status)
    ->orderByRaw("CASE
        WHEN KHKAdressen.Auswertungskennzeichen = 'Tournus' THEN 0
        ELSE 1
    END")
    ->orderBy('PFTankgasBestellungen.timestamp')
    ->get();

Note that I tried to keep the same table and columns' names from your original SQL query

automica

5 years ago

Level 54

@nosean your issue looks like it’s related to the inconsistency in your use of table name.

Some places you call it

PF_tank_gas_orders

and in others

PF_Tank_gas_orders

I suspect the second use is just a typo but it needs to all match and be the exact name of your table

Nosean

5 years ago

Level 1

Hey Thank's rodrigo,

Thank you for your answer. I think that's it, I didn't know the spelling:

 ->join('KHKAdressen', function ($join) {
        $join->on('PFTankgasBestellungen.Adresse', '=', 'KHKAdressen.Adresse');
        $join->on('PFTankgasBestellungen.Mandant', '=', 'KHKAdressen.Mandant');
    })

Thank's to all.

1 like

rodrigo.pedra

5 years ago

Level 56

You're welcome! Great you got it working. Have a nice day =)

Please or to participate in this conversation.