Forum PHP.pl > Subselect w jednej tabeli

Pomoc - Szukaj - Użytkownicy - Kalendarz

Forum PHP.pl > Forum > Bazy danych > MySQL

bojak

16.02.2016, 14:34:14

Witam.

Na wstępie powiem, że do tej pory jakość sobie radziłem z różnymi problemami, ale.. brak mi pomysłu i trochę czas nagli (jest wiele osób, które posiadają ten sam sklep (Shoper lub ClikShop) i ten sam problem, a aktualizacja z poprawką tego szybko pewnie nie nadejdzie znając życie). Nie liczę na gotowe rozwiązanie a chociaż na pokazanie mi drogi do rozwiązania.

Mam sklep internetowy ale niestety nie aktualizują się stany magazynowe produktu głównego na podstawie wariantów. chciałbym:
1. uzyskać listę produktów, które należy zaktualizować - lista muszę wyeksportować do pliku tylko sam product_id w przypadku gdy suma magazynowa wariantów jest różna od stanu magazynowego produktu głównego
2. aktualizacja stanu magazynowego na podstawie stanu magazynowego wariantów

Do rzeczy. Struktura tabeli (część).

[SQL] pobierz, plaintext 
mysql> SELECT `product_id` , `stock`, extended FROM products_stock;
[SQL] pobierz, plaintext

+------------+-------+----------+
| product_id | stock | extended |
+------------+-------+----------+
| 110 | 7 | 0 |
| 110 | 1 | 1 |
| 110 | 1 | 1 |
| 112 | 5 | 0 |
| 112 | 0 | 1 |
| 113 | 9 | 0 |
| 113 | 2 | 1 |
| 113 | 1 | 1 |
| 113 | 2 | 1 |
| 113 | 2 | 1 |
| 115 | 4 | 0 |
| 115 | 1 | 1 |
| 115 | 1 | 1 |
| 115 | 2 | 1 |

extended =0 - produkt główny, 1-wariant produktu

wymyśliłem zapytanie (Osobne zapytania działają, ale nie wiem jak je połączyć w całość):

[SQL] pobierz, plaintext 
SELECT `product_id`,stock,(SELECT sum(stock) FROM products_stock WHERE extended=1 GROUP BY product_id)
FROM products_stock
WHERE extended=0
GROUP BY product_id
[SQL] pobierz, plaintext

chciałem chociaż uzyskać wynik produck_id, stock (główny), sum(stock - wariantów)

ale było by to zbyt piękne by się udało - wynik wyszedł spodziewany - subquery returns more then 1 row.
wynika to z tego, że nie wszystkie produkty główne mają warianty (czyli są product_id gdzie brak jest extended=1)

Rozważałem też wynik z

[SQL] pobierz, plaintext 
SELECT `product_id`,sum(stock) FROM products_stock WHERE extended=1 GROUP BY product_id
[SQL] pobierz, plaintext

zrobić jako tabelę tymczasową (macierz), ale zależy mi na jak najprostszym rozwiązaniu - skrypt będzie uruchamiany co 15-30 minut

Co podobnego już robiłem, ale jak pewne rzeczy robi się raz na rok to się tego nie pamięta.

kapslokk

16.02.2016, 15:00:06

[SQL] pobierz, plaintext 
SELECT 
x.product_id, 
x.stock AS IloscGlownego,
sum(x1.stock) AS IloscWariantow,
(x.stock + sum(x1.stock)) AS Razem
FROM XX x
LEFT JOIN XX x1 ON 
x.product_id = x1.product_id &&
x1.`extended` = 1
WHERE x.`extended` = 0 GROUP BY product_id;
[SQL] pobierz, plaintext

http://sqlfiddle.com/#!9/170eb/8
O coś takiego chodzi? Jeśli nie to podaj prawidłową tablicę wyjściową dla danych z pierwszego postu.

bojak

17.02.2016, 02:18:16

Jest bardzo blisko - będę musiał poprawić

dokładniej to potrzebuję samą listę product_ID gdy IlośćGłównego<>Iloscwariantów -potrzebna do eksportu do pliku i wykorzystane będzie w skrypcie sh który jest już gotowy
a potem muszę zrobić aktualizację ilośćGłówna na podstawie IlośćWariantów

Wrócę z pracy wieczorem to będę przy tym siedział.

Bardzo dziękuję.

Siedzę od 1,5h no i mam dumam jak rozwiązać problem z dodaniem jeszcze jednego warunku.

[SQL] pobierz, plaintext 
SELECT product_id,stock,extended FROM `products_stock` LIMIT 10
[SQL] pobierz, plaintext

product_id | stock | extended
110 | 7 | 0
110 | 1 | 1
110 | 1 | 1
112 | 1 | 0
112 | 0 | 1
113 | 7 | 0
113 | 1 | 1
113 | 2 | 1
113 | 2 | 1
113 | 2 | 1

i teraz chciałbym aby sprawdzić czy x.stok gdzie x.extended=0 jest równy/różny sum(x1.stock) gdzie x1.extended=1. Biorąc sytuację że chciałbym mieć tylko product_ID gdy x.stock<>sum(x1.stock) to tabela powinna wyglądać tak

product_id | IloscGlownego | IloscWariantow
110 | 7 | 2
112 | 1 | 0

113 - nie wyświetla się bo jest ok - IloscGlownego=7 i suma IloscWariantow =7

Podany kod działa dobrze, ale tylko gdy wyświetlamy. Próbowałem na różne kombinacje ale niestety nie wychodzi mi. Dopisanie warunku

[SQL] pobierz, plaintext 
AND 'IloscGlownego'<>'IloscWariantow'
[SQL] pobierz, plaintext

niestety nie zmienia nic. Próbowałem z wykorzystaniem HAVING.i też nic

[SQL] pobierz, plaintext 
SELECT x.product_id, x.stock AS IloscGlownego ,sum(x1.stock) AS IloscWariantow
FROM products_stock x
RIGHT JOIN products_stock x1 
ON x.product_id=x1.product_id && x1.extended=1
WHERE x.extended=0
GROUP BY product_id
HAVING  'x.stock'<>(SELECT sum('x1.stock') FROM products_stock x1 WHERE x1.extended=1  GROUP BY product_id LIMIT 1)
[SQL] pobierz, plaintext

Otrzymuję nic

Czy może jakaś sugestia

a jednak działa z having - inaczej zapisane i działa

[SQL] pobierz, plaintext 
SELECT 
x.product_id, 
x.stock AS IloscGlownego,
sum(x1.stock) AS IloscWariantow
 
FROM products_stock x
LEFT JOIN products_stock x1 ON 
x.product_id = x1.product_id && x1.`extended` = 1
WHERE x.`extended` = 0 
GROUP BY product_id
HAVING x.stock<>sum(x1.stock)
[SQL] pobierz, plaintext

teraz tylko kod do aktualizacji IloscGlownego na podstawie IloscWariantow.

trueblue

17.02.2016, 10:35:51

Pod warunkiem, że główny jest tylko jeden:

[SQL] pobierz, plaintext 
SELECT product_id, SUM(IF(extended=0,stock,0)) AS main, SUM(IF(extended=1,stock,0)) AS extended
FROM product
GROUP BY product_id
HAVING main<>extended
[SQL] pobierz, plaintext

bojak

18.02.2016, 12:30:20

Mam pytanie jeszcze z innej beczki.

napisałem zapytanie które tworzy plik ze ścieżką do usuwania plików (skrypt basch)

[SQL] pobierz, plaintext 
SELECT concat('find /home/bojak/domains/image-collection.pl/public_html/environment/cache/objects/ -type f -name "zend_cache---productStock_',stock_id,'" -exec rm {} \\;')
[SQL] pobierz, plaintext

problem dotyczy ostatnich znaków "\\". prawidłowa komenda to

[SQL] pobierz, plaintext 
-exec rm {} \;
[SQL] pobierz, plaintext

. Problem jest taki, że jak na końcu polecenia wpiszę jeden \ to baza wywala błąd, skolei jak piszę \\ to w PHPMyadmin widzę tylko jedno \, natomiast jak wysyłam to do pliku to otrzymuję też \\. potem muszę użyć dodatkowego polecenia

[HTML] pobierz, plaintext 
sed -i 's/\\;/\; /g' 1-wariant-zero.out.sh
[HTML] pobierz, plaintext

do usuwania jednego \.

czy jest jakieś rozwiązanie bez dodatkowych poleceń tak by w pliku zapisał się \. Próbowałem zastąpić \ poleceniem char(92). Ale jest tak samo w PHPmyadmin jest \ a w pliku \\

Cytat(trueblue @ 17.02.2016, 10:35:51 )

Pod warunkiem, że główny jest tylko jeden:

[SQL] pobierz, plaintext 
SELECT product_id, SUM(IF(extended=0,stock,0)) AS main, SUM(IF(extended=1,stock,0)) AS extended
FROM product
GROUP BY product_id
HAVING main<>extended
[SQL] pobierz, plaintext

Jest pięknie napisane, ale wynik nie uwzględnia produktów głównych, które nie mają wariantów - a niestety takie tez bywają.

trueblue

18.02.2016, 12:38:18

Cytat(bojak @ 18.02.2016, 12:30:20 )

Jest pięknie napisane, ale wynik nie uwzględnia produktów głównych, które nie mają wariantów - a niestety takie tez bywają.

[SQL] pobierz, plaintext 
SELECT product_id, SUM(IF(extended=0,stock,0)) AS main, SUM(IF(extended=1,stock,0)) AS extended
FROM product
GROUP BY product_id
HAVING main<>extended OR extended=0
[SQL] pobierz, plaintext

Jeśli nie jest poprawnie, podaj przykładowy rekord dla głównego bez wariantów.

bojak

18.02.2016, 15:04:15

No właśnie problem w tym, że w tej tabeli nie ma żadnego znacznika że produkt ma warianty. przeglądałem inne tabele i też nic nie zauważyłem.

[HTML] pobierz, plaintext 
product_id | stock | extended
140 | 7 | 0
141 | 1 | 0
141 | 1 | 1
[HTML] pobierz, plaintext

produkt główny 140 nie ma wariantów, produkt 141 ma 1 wariant

trueblue

18.02.2016, 15:32:37

Sprawdzałeś zapytanie, które podałem wyżej?

bojak

18.02.2016, 16:08:17

Cytat(trueblue @ 18.02.2016, 12:38:18 )

[SQL] pobierz, plaintext 
SELECT product_id, SUM(IF(extended=0,stock,0)) AS main, SUM(IF(extended=1,stock,0)) AS extended
FROM product
GROUP BY product_id
HAVING main<>extended OR extended=0
[SQL] pobierz, plaintext

Jeśli nie jest poprawnie, podaj przykładowy rekord dla głównego bez wariantów.

Niestety też nie do końca działa bo pokazuje wynik zawierający produkty, których produkt główny i wariant tego produktu =0, ale wpadłem na inny pomysł. skoro produkty główne występują zawsze 1 raz a produkty z wariantami więcej niż 1 to po prostu je zliczyłem

[SQL] pobierz, plaintext 
SELECT stock_id,product_id, count(product_id) AS zlicz, IF(extended=0,stock,0) AS main, SUM(IF(extended=1,stock,0)) AS extended
    FROM products_stock
    GROUP BY product_id
    HAVING main<>extended AND zlicz>1
[SQL] pobierz, plaintext

i działa

trueblue

18.02.2016, 16:16:53

W ten sposób nie pokaże produktu o id=140, który nie ma wariantów.

bojak

18.02.2016, 16:58:31

O to mi właśnie chodziło - jak pisałem wcześniej. Być może mało treściwie bo mam problem z przekazywaniem informacji - niestety.
Zależy mi, aby z bazy wyciągnąć dane gdzie:

stock where expended=0 <> sum(stock) where expended=1.

Potrzebuję taką listę z product_id do wysłana do pliku na podstawie którego będą usuwane pliki z cache. bez tego zmiany w bazie nie są widoczne w sklepie. Pewnie można to zrobić kodem php, ale ja nie jestem programistą php,html.

na podstawie takiego pobrania danych, będę pisał update żeby suma stanu wariantów nadpisała się na stan magazynowy produktu głównego.

Za nim problem będzie rozwiązany przez sklep shopera mnogą potrwać miesiące a czasem lata, a pilnowanie stanów ręcznie to strata czasu. Jak mam trochę czasu to zawsze wychylam się i wysyłam na formu shopera jakieś rozwiązanie problemu- trochę prowizoryczne, ale działa

trueblue

18.02.2016, 17:03:36

Cytat(bojak @ 18.02.2016, 12:30:20 )

[...] wynik nie uwzględnia produktów głównych, które nie mają wariantów - a niestety takie tez bywają.

Nie wiem czy to jest mało treściwie:)
Ale jeśli teraz działa, to ok.

bojak

18.02.2016, 23:45:02

Cytat(trueblue @ 18.02.2016, 17:03:36 )

Nie wiem czy to jest mało treściwie:)
Ale jeśli teraz działa, to ok.

No właśnie

sam wprowadziłem w błąd. dziękuję bardzo za za pomoc i za angażowanie - a w tym chodziło mi że skrypt UWZGLĘDNIA produkty bez wariantów. co innego myślałem, a co innego pisałem - to wszystko przez brak snu 2-5h dziennie

Czy mógł bym prosić o rzucenie okiem w wolnej chwili na zapytanie UPDATE

[SQL] pobierz, plaintext 
UPDATE products_stock m JOIN
(SELECT stock_id,product_id, count(product_id) AS zlicz, IF(extended=0,stock,0) AS main, SUM(IF(extended=1,stock,0)) AS extended
    FROM products_stock
    GROUP BY product_id
    HAVING main<>extended AND zlicz>1 
) e
ON m.stock_id=e.stock_id
SET m.stock=e.extended
[SQL] pobierz, plaintext

testowałem na kopi i teoretycznie działa poprawnie, ale jak by ktoś mógł rzucić fachowym okiem na poprawność

To jest wersja lo-fi głównej zawartości. Aby zobaczyć pełną wersję z większą zawartością, obrazkami i formatowaniem proszę kliknij tutaj.