# Datenbank und Joins

**URL:** <https://forum.shopware.com/t/datenbank-und-joins/87769>\
**Category:** Plugins\
**Tags:** general, programming\
**Created:** [19. Mai 2021 um 11:52 UTC](https://forum.shopware.com/t/datenbank-und-joins/87769 "2021-05-19T11:52:50Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![DemianSnow](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.shopware.com/demiansnow/32/13437_2.png) [@DemianSnow](https://forum.shopware.com/u/DemianSnow)\
**Post date:** [19. Mai 2021 um 11:52 UTC](https://forum.shopware.com/t/datenbank-und-joins/87769/1 "2021-05-19T11:52:50Z")

</div>

Moinsen,  
wir haben folgendes Problem:  
Wir haben schon eine fertige SQL abfrage. Jetzt Tippern wir uns mittlerweile tot wie man diese nun in PHP schreiben kann:

select  
s\_articles\_details.ordernumber as ArtikelNR,  
s\_articles\_details.articleID as ArtikelID,  
s\_articles\_details.instock as Warenbestand,  
s\_articles\_details.ean as EAN,  
(sum(s\_articles\_prices.price\*s\_core\_tax.tax)/100+s\_articles\_prices.price) as VK  
from  
s\_articles  
left join s\_articles\_details on s\_articles.ID=s\_articles\_details.articleID  
left join s\_articles\_prices on s\_articles\_prices.articleID=s\_articles.id  
left join s\_core\_tax on s\_core\_tax.ID=s\_articles.taxID  
group by s\_articles.id

Unser Versuch schaut so aus:  
$connection-\>select(  
‚s\_articles\_details.ordernumber as ArtikelNR‘,  
‚s\_articles\_details.articleID as ArtikelID‘,  
‚s\_articles\_details.instock as Warenbestand‘,  
‚s\_articles\_details.ean as EAN‘,  
‚(sum(s\_articles\_prices.price\*s\_core\_tax.tax)/100+s\_articles\_prices.price) as VK‘)  
-\>from(‚s\_articles‘, ‚details‘)  
-\>leftJoin(‚details‘ , ‚s\_articles\_details‘,‚aa‘,‚s\_articles.ID=s\_articles\_details.articleID‘)  
-\>leftJoin(‚details‘, ‚s\_articles\_prices‘ , ‚bb‘ , ‚s\_articles\_prices.articleID=s\_articles.id‘)  
-\>leftJoin(‚details‘, ‚s\_core\_tax‘ , ‚cc‘ , ‚s\_core\_tax.ID=s\_articles.taxID‘)  
-\>groupBy(‚ArtikelID‘);

Sobald wir die Joins einbauen, kommt folgender Fehler:  
**Fatal error** : Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‚s\_articles\_prices.price‘ in ‚field list‘ in /…

---

<div class="post-metadata">

**Author:** ![brettvormkopp](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.shopware.com/brettvormkopp/32/7788_2.png) [@brettvormkopp](https://forum.shopware.com/u/brettvormkopp)\
**Post date:** [19. Mai 2021 um 12:16 UTC](https://forum.shopware.com/t/datenbank-und-joins/87769/2 "2021-05-19T12:16:38Z")

</div>

Der JOIN für s\_articles vermisse ich, und auch groß und kleinschreibung.

IN PHP setze ich das so um:

```php
$query = "SELECT
sad.ordernumber AS ARTIKELNR,
sad.articleID AS ARTIKELID,
sad.instock AS Warenbestand,
sad.ean AS ean,
ROUND((sap.price*sct.tax)/100+sap.price,2) AS VK
FROM s_articles_details AS sad
INNER JOIN s_articles AS sa ON sa.id = sad.articleID
INNER JOIN s_articles_prices AS sap ON sap.articleID = sa.id
INNER JOIN s_core_tax AS sct ON sct.id = sa.taxID";

$dbconfig = include __DIR__. "/config.php";
$pdo = new PDO('mysql:host=127.0.0.1;dbname='.$dbconfig["db"]["dbname"], $dbconfig["db"]["username"], $dbconfig["db"]["password"]);
$connection = $pdo->prepare($query);
$connection->execute();
$result = $connection->fetchAll(PDO::FETCH_ASSOC);

```

PS: ausserdem steht SUM() nicht in jeder DB einfach so zur Verfügung

---

<div class="post-metadata">

**Author:** ![DemianSnow](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.shopware.com/demiansnow/32/13437_2.png) [@DemianSnow](https://forum.shopware.com/u/DemianSnow)\
**Post date:** [19. Mai 2021 um 12:42 UTC](https://forum.shopware.com/t/datenbank-und-joins/87769/3 "2021-05-19T12:42:21Z")

</div>

Und wie würde das in unserer Schreibweise aussehen?

Da wir das ja etwas anderes verwenden. Der Vorschlag von brettvormkopp geht bei uns nicht.

Evtl. noch da dazu wir bauen unsere db Connection mit:  
$connection = Shopware()-\>Container()-\>get(‚dbal\_connection‘)-\>createQueryBuilder();  
auf.

@brettvormkopp kannst du es evtl. etwas genauer beschreiben, dass wir es umbauen können?

---

<div class="post-metadata">

**Author:** ![brettvormkopp](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.shopware.com/brettvormkopp/32/7788_2.png) [@brettvormkopp](https://forum.shopware.com/u/brettvormkopp)\
**Post date:** [19. Mai 2021 um 12:48 UTC](https://forum.shopware.com/t/datenbank-und-joins/87769/4 "2021-05-19T12:48:38Z")

</div>

```php
$query = "SELECT
sad.ordernumber AS ARTIKELNR,
sad.articleID AS ARTIKELID,
sad.instock AS Warenbestand,
sad.ean AS ean,
ROUND((sap.price*sct.tax)/100+sap.price,2) AS VK
FROM s_articles_details AS sad
INNER JOIN s_articles AS sa ON sa.id = sad.articleID
INNER JOIN s_articles_prices AS sap ON sap.articleID = sa.id
INNER JOIN s_core_tax AS sct ON sct.id = sa.taxID";

$connection = Shopware()->Container()->get('dbal_connection');
$data = $connection->fetchAll($query);

```

[https://developers.shopware.com/developers-guide/cheat-sheet/](https://developers.shopware.com/developers-guide/cheat-sheet/)

---

<div class="post-metadata">

**Author:** ![DemianSnow](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.shopware.com/demiansnow/32/13437_2.png) [@DemianSnow](https://forum.shopware.com/u/DemianSnow)\
**Post date:** [19. Mai 2021 um 12:53 UTC](https://forum.shopware.com/t/datenbank-und-joins/87769/5 "2021-05-19T12:53:47Z")

</div>

$connection = Shopware()-\>Container()-\>get(‚dbal\_connection‘)-\>createQueryBuilder();

```
    $connection->select(
        'sad.ordernumber AS ARTIKELNR',
                'sad.articleID AS ARTIKELID',
                'sad.instock AS Warenbestand',
                'sad.ean AS ean',
            'ROUND((sap.price*sct.tax)/100+sap.price,2) AS VK')
        ->from('s_articles_details', 'sad')
        ->innerJoin('sad' , 's_articles','sa','sa.ID = sad.articleID')
        ->innerJoin('sad', 's_articles_prices' , 'sap' , 'sap.articleID = sa.id')
        ->innerJoin('sad', 's_core_tax' , 'sct' , 'sct.ID = sa.taxID')
        ->groupBy('ArtikelID');

```

So schaut es jetzt bei uns aus und es Funktioniert. Vielen dank für die Hilfe 🙂

---

<div class="post-metadata">

**Author:** ![R4M](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.shopware.com/r4m/32/9983_2.png) [@R4M](https://forum.shopware.com/u/R4M)\
**Post date:** [25. Mai 2021 um 11:57 UTC](https://forum.shopware.com/t/datenbank-und-joins/87769/6 "2021-05-25T11:57:55Z")

</div>

Ich habe jetzt nicht das Query ausprobiert, aber ICH würde mind. bei der Tabelle „s\_articles\_prices“ nicht nach „articleID“ sondern nach „articledetailsID“ gehen. Die jetzige Abfrage könnte bei Varianten-Artikeln Probleme verursachen.

---

<div class="post-metadata">

**Author:** ![brettvormkopp](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.shopware.com/brettvormkopp/32/7788_2.png) [@brettvormkopp](https://forum.shopware.com/u/brettvormkopp)\
**Post date:** [26. Mai 2021 um 12:48 UTC](https://forum.shopware.com/t/datenbank-und-joins/87769/7 "2021-05-26T12:48:33Z")

</div>

Guter Tipp. Wir haben keine Varianten, aber es ist besser man ist auf den Fall vorbereitet.