Chisum asked 5 years ago
This is the solution I came up with, using a table as a view for the query then having grid run on it. However it leaves me unsatisfied because now I either have to worry about syncing the data all the time or increase loading time a lot since there’s a ton of records.
The problem is there’s this inventory table that doesn’t have a row or every product, only ones it has been selected you manage manually but you still want to be able to search and edit any entry.
As I said this fixes that problem. But it creates the other ones. Here’s an idea of the code, and it works, as long as I change all the on_update code to work right which I did.
I just don’t like the overhead of the load, as it was really fast without this.
create table $tablename (
item_id int unsigned not null auto_increment,
image varchar(255) default ”,
name varchar(255) default ”,
sku varchar(50) default ”,
qty int not null default 0,
cost decimal(10,2) not null default 0,
sale decimal(10,2) not null default 0,
available_qty int not null default 0,
ship_qty int not null default 0,
room_shelf varchar(10) default ”,
stock_status varchar (60) not null default ‘Out of Stock’,
product_id int unsigned not null default 0,
warehouse_id int unsigned not null default 0,
manufacturer varchar (255) not null default ”,
primary key(item_id)
);
insert into $tablename
select
null,
concat(‘https://domain.com/media/catalog/product’,IF(p.image=’no_selection’,’/placeholder/default/noimagesmall.png’,p.image)) as image,
p.name,p.sku,COALESCE(i.qty,0) as qty,COALESCE(i.cost,0) as cost,COALESCE(i.sale,0) as sale,COALESCE(i.available_qty,0) available_qty,COALESCE(i.ship_qty,0) as ship_qty,COALESCE(i.room_shelf,”) as room_shelf,
(CASE WHEN i.stock_status <> 0 THEN ‘In Stock’ ELSE ‘Out Of Stock’ END) as stock_status,
p.entity_id as product_id,$curr_warehouse as warehouse_id,
eaov.value as manufacturer
from
catalog_product_flat_1 p
left join amasty_multiinventory_warehouse_item i on (i.product_id=p.entity_id or i.product_id is null) and (i.warehouse_id in ($curr_warehouse,null))
left join catalog_product_entity_int as pi on pi.attribute_id=83 and pi.entity_id=p.entity_id
left join eav_attribute_option_value as eaov on pi.value = eaov.option_id and eaov.store_id=0
Abu Ghufran Staff answered 5 years ago
Hello,
I am unable to understand your question completely. It looks like there is some performance issue in using magento table with plugin amasty.
Apparently what i undestand, it is not related to phpgrid library functionality and magento support should be contacted to found a better optimized query solution. Datagrid only support displaying of sql-query you provide in select_command property. How you are forming query is out of the scope of datagrid.
Chisum answered 5 years ago
It has nothing to do with other tables performance. It\’s to do with the solution I wish to make. The tables are large. Any large table it slow to create a full join compared to a small one. Large means they have a lot of records. What I\’d like to do is instead of creating a view table for the join, just do the join. The joins are limited to the number of records so are slow. Creating the view before the person uses it is slow. Make sense? Selecting 100 rows from the join is faster than selecting all the rows of the join and inserting them into a view table. The issue preventing me from doing that is I don\’t know if phpgrid can function off such a complicated join. One of the things the join has to do, is do a left join so even if there isn\’t a row in the warehouse inventory table (because the code treats if that warehouse for that item exists it means stock is managed for that item which changes other behaviors so i can\’t fix this with empty rows, and empty rows would also add a lot of size to the database because of how many items x warehouses there are). So how does phpgrid handle left joins, is basically what I need to know here. Table P has every product in the database. Table W has some warehouses with some products. PHP grid needs to be able to search every product P, which an inner join can\’t make happen between P and W, so a left join is required. So if P left joins W, product_id for instance would exist in every row, but warehouse_id would be null for many rows. So now you have this joined table in phpgrid, what happens when you go to update a null row? Seems php grid wouldn\’t know what to do, it\’d need to create a row in W, not edit it. But I can\’t just have an inner join and let people add rows, as I said, every product must be searchable.
Abu Ghufran Staff answered 5 years ago
Datagrid support LEFT JOIN as well. You can put your complex join query in select_command and listing / searching / sorting should work as expected. If some field is ambiguous, then table.field should be used in $col[“dbname”] property, like:
$col[“dbname”] = “W.product_name”;
Now for insert,update,delete … datagrid by default do these operations on single table provided in ->table property. If you have complex update logic, then you can use on_update event handler and put your own php code to update multiple database tables as required.
Refer docs: http://www.phpgrid.org/docs/grid-events/
Let me know in case of further query.
