Výběr dat z více tabulek (uživatelská cena k produktu)

Tu je nejaky pokus, avsak ako chces selectovat data?
Tento model nizsie ti zduplikuje produkty tolkokrat, kolko je (pocet cennikov * pocet uzivatelov), preto je vhodne vyberat cennik, uzivatela a nasledne az produkty (WHERE statement)

$user = 'user'; // u
$product = 'product'; // p
$product_price = 'product_price'; // pp
$pricelist = 'pricelist'; // pl

$product_code = 'CODE-XXX'; // vyber produktu podla code

$this->database->query('
SELECT
pl.name as pricelist_name,
u.name as user_name,
pp.price as user_price,
p.*
FROM
?name p
LEFT JOIN ?name pp ON p.id = pp.product_id' . // pocet_riadkov = (pocet_produktov * pocet_cennikov) + pocet_produktov_bez_cennika
'LEFT JOIN ?name u ON pp.pricelist_id = u.pricelist_id' . // pocet_riadkov = (pocet_produktov * pocet_cennikov * pocet_uzivatelov) + pocet_produktov_bez_cennika
'LEFT JOIN ?name pl ON pp.pricelist_id = pl.id' .  // pocet_riadkov = (pocet_produktov * pocet_cennikov * pocet_uzivatelov) + pocet_produktov_bez_cennika

'WHERE p.code = ?

',$product,$product_price,$user,$pricelist,$product_code)->fetchAll();

Pri tabulkach product, user a pricelist predpokladam, ze ID je unikatne.

Ked pozuijes WHERE p.code, tak sa ti zahrnu aj produkty, ktore nemusia mat cennik, pripadne produkty s cennikom, ktore nemusia mat uzivatela.

MODEL:

<?php

namespace App\Model;

use Nette;
use Nette\Caching;

class Repository extends Nette\Object
{

    const P_TBL = 'product';
    const PP_TBL = 'product_price';
    const U_TBL = 'user';
    const PL_TBL = 'pricelist';

    /** @var Nette\Database\Context */
    private $database;

    /** @var Caching\Cache */
    private $caching;

    public function __construct(Nette\Database\Connection $database, Caching\IStorage $storage)
    {
        $this->database = $database;
        $this->caching = new Caching\Cache($storage, 'Repository');
    }
    public function getData($debug = FALSE)
    {
        $data = NULL;
        $key = 'product-pricelist-cache'
        if ($debug == FALSE)
        {
            $data = $this->caching->load($key);
        }
        if ($data === null) {
            $data = $this->database->query('
				SELECT
                u.name as user_name,
                pp.pricelist_id as pricelist_id,
				pp.price as new_user_price,
				p.*
				FROM
				?name p
				LEFT JOIN ?name pp ON p.id = pp.product_id' . // pocet_riadkov = (pocet_produktov * pocet_cennikov) + pocet_produktov_bez_cennika
                'LEFT JOIN ?name u ON pp.pricelist_id = u.pricelist_id' . // pocet_riadkov = (pocet_produktov * pocet_cennikov * pocet_uzivatelov) + pocet_produktov_bez_cennika
                'LEFT JOIN ?name pl ON pp.pricelist_id = pl.id' .  // pocet_riadkov = (pocet_produktov * pocet_cennikov * pocet_uzivatelov) + pocet_produktov_bez_cennika
				,self::P_TBL,self::PP_TBL,self::U_TBL,self::PL_TBL)->fetchAll();

            $this->caching->save($key, $data, [Caching\Cache::EXPIRE => '20 minutes']); // refresh time
        }
        return $data;
    }
    public function getProductUserPrice($user_name, $pricelist_id, $product_id, $debug = FALSE) // FALSE = vynecha cache v getData()
    {
        $filter = [];
        $data = $this->getData($debug);
        if (!is_null($data)) {
            foreach ($data as $i) {
                if ($i['user_name'] == $user_name  AND $i['pricelist_id'] == $pricelist_id AND $i['id'] == $product_id) {
                    $filter = $i;
                }
            }
        }
        return $filter;
	}
}

PRESENTER:

<?php

namespace App\Presenters;

use Nette;
use App\Model\Repository as repo;

class ProductPresenter extends Nette\Application\UI\Presenter
{
    /** @var repo */
    protected $productpriceRepo;

    public function __construct(repo $productpriceRepo)
    {
        parent::__construct();
        $this->productpriceRepo = $productpriceRepo;
    }

    protected function startup()
    {
        parent::startup();
        if (!$this->getUser()->isLoggedIn()) {
            $this->redirect('Home:default');
        }
    }

    public function renderProductPrice($pricelist_id, $product_id, $debug = FALSE)
    {
        $user_id = $this->getUser()->getId(); // v tomto pripade je id = meno a je definovane priamo v config.neon
        $data = $this->productpriceRepo->getProductUserPrice($user_id, $pricelist_id, $product_id, $debug);
        $this->template->data = $data;
        $this->template->user_id = $user_id;
    }
}

CONFIG.NEON:

security:
    users:
        user_id: tazke-heslo
database:
	default:
		dsn: 'mysql:host=localhost;dbname=public'
		user: root
		password:

services:
	- App\Model\Repository

LATTE:

{block content}
    <h1>Renderovana tabulka cien pre {$user_id}:</h1>
    {if count($data)>0}
        <table>
            <tr>
                <td><b>PRODUCT_ID</b></td>
                <td><b>PRICE</b></td>
                <td><b>USER_PRICE</b></td>

                {*}     a tak dalej ....        {*}

            </tr>
            {foreach $data as $i}
                    <tr>
                        <td>{$i['id']}</td>
                        <td>{$i['price']}</td>
                        <td>{$i['user_price']}</td>

                        {*}     a tak dalej ....        {*}

                    </tr>
            {/foreach}
        </table>
    {else}
    {/if}
{/block}

Hadam pomoze aspon trochu :)
Riesim to jednym dotazom na DB, ak to nevyhovuje, bude musiet pomoct este niekto iny, zatial DB explorer a DiBi moc neovladam :)

Potom si iba zavolaj:

localhost/product/productprice?pricelist_id=&product_id=

pripadne pre vypnutie cacheovania:

localhost/product/productprice?pricelist_id=&product_id=&debug=1 // $debug = TRUE

PS: ak su tam chyby, tak sa ospravedlnujem, netestoval som to

OpenCartem bych se radši moc neinspiroval… a konstruktor ve finálních presenterech (MujPresenter) je určitě čistší řešení než inject. Inject se používá jen v abstraktních předcích (BasePresenter).

Editoval CZechBoY (16. 5. 2018 15:03)