基于 EAV 的库存控制应用程序的 MySQL 自连接

发布于 2024-09-13 13:27:03 字数 1129 浏览 11 评论 0原文

这个问题与我在架构有关“https://stackoverflow.com/questions/3410362/design-of-an-eav-or-class-concrete-table-inheritance-database-for-stock-contr">有关库存控制的原始问题应用。

我正在尝试创建一个 MySQL 查询来提供特定商品的当前库存。

查询正在运行，但我想知道是否有更有效的方法来获取我需要的信息。

SELECT 's'.*,
    'v1'.'attribute_id' AS 'att1',
    'v1'.'value' AS 'val1'
    'v2'.'attribute_id' AS 'att2',
    'v2'.'value' AS 'val2'
FROM 'eav_ev' AS 'ev1'
INNER JOIN 'stock' AS 's' ON s.id = ev1.stock_id
INNER JOIN 'eav_ev' AS 'ev2' ON ev1.stock_id = ev2.stock_id
INNER JOIN 'eav_value' AS 'v1' ON v1.id = ev1.value_id
INNER JOIN 'eav_value' AS 'v2' ON v2.id = ev2.value_id
WHERE (ev1.entity_id = '45')
    AND (ev1.value_id <> ev2.value_id)
    AND (s.total > 0)
GROUP BY 'ev1'.'stock_id'
ORDER BY 'ev1'.'value_id' ASC

的内容

array (1) {
    [0] => array(5) {
        ["stock_id"] => "2"
        ["att1"] => "3"
        ["val1"] => "M12"
        ["att2"] => "4"
        ["val2"] => "45"
    }
}

这会返回类似“看起来很混乱，但我可怜的大脑无法想出更好的东西”

。有什么建议吗？

原文

This question relates to the schema I suggested in my original question regarding a stock control application.

I'm trying to create a MySQL query that provides the current stock for a particular item.

The query is working but I wondered whether there is a more efficient way of obtaining the information I require.

SELECT 's'.*,
    'v1'.'attribute_id' AS 'att1',
    'v1'.'value' AS 'val1'
    'v2'.'attribute_id' AS 'att2',
    'v2'.'value' AS 'val2'
FROM 'eav_ev' AS 'ev1'
INNER JOIN 'stock' AS 's' ON s.id = ev1.stock_id
INNER JOIN 'eav_ev' AS 'ev2' ON ev1.stock_id = ev2.stock_id
INNER JOIN 'eav_value' AS 'v1' ON v1.id = ev1.value_id
INNER JOIN 'eav_value' AS 'v2' ON v2.id = ev2.value_id
WHERE (ev1.entity_id = '45')
    AND (ev1.value_id <> ev2.value_id)
    AND (s.total > 0)
GROUP BY 'ev1'.'stock_id'
ORDER BY 'ev1'.'value_id' ASC

This returns something along the lines of

array (1) {
    [0] => array(5) {
        ["stock_id"] => "2"
        ["att1"] => "3"
        ["val1"] => "M12"
        ["att2"] => "4"
        ["val2"] => "45"
    }
}

It seems very messy but my poor brain is incapable of coming up with something better.

Any suggestions?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

把回忆走一遍 2024-09-20 13:27:03

如果您首先存储属性列表，则也可以使用 value AS attribute_X，而不是使用 attribute_id AS att1。您可以简单地缓存查询，然后您可以在 1 个清晰的查询中选择所有需要的数据。

假设您首先获取了属性 ID 列表（即 SELECT attribute_id FROM eav_value），请选择以下选项：

SELECT
    v1.value_id AS attribute_1 -- (or whatever the ID was fetched in the first query)
    v2.value_id AS attribute_2 -- (or whatever the second ID was fetched in the first query)
...

Instead of using attribute_id AS att1 you could also use value AS attribute_X if you store a list of attributes first. You can simply cache the query after which you can just select all needed data in 1 clear query.

Assuming you've fetched a list of attribute IDs first (i.e. SELECT attribute_id FROM eav_value), select this:

SELECT
    v1.value_id AS attribute_1 -- (or whatever the ID was fetched in the first query)
    v2.value_id AS attribute_2 -- (or whatever the second ID was fetched in the first query)
...

回复收藏 0 原文

~没有更多了~