代码之家 › 专栏 › 技术社区 › lejahmie

针对MYSQL的高级many2many查询

many-to-many select mysql php

2

lejahmie · 技术社区 · 16 年前

我正在尝试建立一个图片库,在那里人们可以访问不同的组,组决定什么目录和图像,他们被允许看到。
我认为许多结构是最好的。

到目前为止,我设法建立这样的数据库:

图像(图像名称、图像文件、图像id)
目录(目录id,目录名称)
供应商(供应商id、供应商名称)
用户(姓名、用户名)
image2cataloge(图像id,目录id)
图像2供应商(图像id、供应商id)
目录2供应商(目录id,供应商id)
用户2供应商(用户id、供应商id)

所以。。。也就是说,保存图片并制作供应商(或组,如果你想),添加用户到供应商,并链接图片到供应商和目录是没有问题的。插入没有问题。
但是,根据用户供应商的设置和他们所处的目录来选择正确的图像比较困难。

例如,我有一个用户具有用户id 1,该用户可以访问供应商id 1。供应商1有权查看目录1,目录1保存有图像1和2的图像。
但供应商只能访问映像id 2。
所有这些设置都存储在数据库中。如何进行选择查询?

这是我测试过的;

//$catalog\u id是我们所在的catalog\u id
//$user_id是当前用户的用户id

$sql = "SELECT i.*
FROM image i, user u, catalog cat, supplier s, supplier2user s2u, supplier2catalog s2c, image2catalog i2c, image2supplier i2s
WHERE u.id = '".$user_id."'
AND s2u.user_id = '".$user_id."'
AND s2u.supplier_id = s.id

AND s2c.catalog_id = '".$catalog_id."' 
AND i2c.catalog_id = '".$catalog_id."'

AND i2s.supplier_id = s.id
AND s2c.supplier_id = s.id

GROUP BY i.id
ORDER BY i.name ASC

但当我添加了多个图像时,所有目录中的所有用户都会看到所有图像。

编辑(2010/02/05):
好的,所以我已经找到了至少在正确的目录中显示正确图像的方法。我是这样做的:

$sql = "SELECT i.*
    FROM 
        image i
    INNER JOIN image2catalog i2c

    ON i.id = i2c.image_id
    AND i2c.catalog_id = '".$pages_level_0[$i]['id']."'

    GROUP BY i.id
    ;";

这让我输出属于用户当前正在访问的目录中的正确图像。现在我只需要编辑这个查询来过滤掉用户没有权限访问的所有图像。我非常感谢你能提供任何帮助!

编辑2010/02/09:
---
创建方案

CREATE TABLE `user` (
  `id` int(11) NOT NULL auto_increment,
  `email` varchar(250) NOT NULL,
  `password` varchar(50) NOT NULL
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE `imagebank` (
      `id` int(11) NOT NULL auto_increment,
      `name` varchar(250) character set utf8 NOT NULL default '',
      `url` varchar(250) character set utf8 NOT NULL default '',
      `childof` varchar(250) character set utf8 NOT NULL default '',
      `hide` tinyint(4) NOT NULL,
      `publishdate` varchar(14) NOT NULL,
      `expiredate` varchar(14) NOT NULL,
      `editdate` varchar(14) NOT NULL,
      `editbygroup` varchar(250) NOT NULL,
      `openby` varchar(250) NOT NULL,
      `opendate` varchar(250) NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;

    CREATE TABLE `imagebank_image` (
      `id` int(11) NOT NULL auto_increment,
      `name` varchar(250) NOT NULL,
      `img` varchar(250) NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

    CREATE TABLE `imagebank_image2catalog` (
      `image_id` int(11) NOT NULL,
      `catalog_id` int(11) NOT NULL,
      PRIMARY KEY  (`image_id`,`catalog_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    CREATE TABLE `imagebank_image2supplier` (
      `image_id` int(11) NOT NULL,
      `supplier_id` int(11) NOT NULL,
      PRIMARY KEY  (`image_id`,`supplier_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    CREATE TABLE `imagebank_supplier` (
      `id` int(11) NOT NULL auto_increment,
      `name` varchar(250) NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

    CREATE TABLE `imagebank_supplier2catalog` (
      `supplier_id` int(11) NOT NULL,
      `catalog_id` int(11) NOT NULL,
      PRIMARY KEY  (`supplier_id`,`catalog_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    CREATE TABLE `imagebank_supplier2user` (
      `supplier_id` int(11) NOT NULL,
      `user_id` int(11) NOT NULL,
      PRIMARY KEY  (`supplier_id`,`user_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

一些数据:

INSERT INTO `imagebank` (`id`, `name`, `url`, `childof`, `hide`, `publishdate`, `expiredate`, `editdate`, `editbygroup`, `openby`, `opendate`) VALUES
(1, 'Test 1', 'test-1', '', 0, '20100204230233', '', '', '', '', ''),
(2, 'Test 2', 'test-2', '', 0, '20100204230244', '', '', '', '', '');

INSERT INTO `imagebank_image` (`id`, `name`, `img`) VALUES
(1, 'Test img 1', 'labb_9noq80bik5.jpeg'),
(2, 'Test img 2', 'labb_53626114dz.jpeg');

INSERT INTO `imagebank_image2catalog` (`image_id`, `catalog_id`) VALUES
(1, 1),
(2, 2);

INSERT INTO `imagebank_image2supplier` (`image_id`, `supplier_id`) VALUES
(1, 2),
(2, 1);

INSERT INTO `imagebank_supplier` (`id`, `name`) VALUES
(1, 'Supplier1'),
(2, 'Supplier2'),
(3, 'Supplier3');

INSERT INTO `imagebank_supplier2catalog` (`supplier_id`, `catalog_id`) VALUES
(1, 2),
(2, 1);

INSERT INTO `imagebank_supplier2user` (`supplier_id`, `user_id`) VALUES
(1, 1),
(1, 11),
(1, 12),
(2, 1),
(2, 10),
(3, 1);

INSERT INTO `user` (`id`, `email`, `password`) VALUES
(1, 'User1@test.com', 'ff02dd5s33taa2ba5ff7c2c4d3327e444'),
(10, 'User2@test.com', 'ff02dd5s33taa2ba5ff7c2c4d3327e444'),
(11, 'User3@test.com', 'ff02dd5s33taa2ba5ff7c2c4d3327e444'),
(12, 'User4@test.com', 'ff02dd5s33taa2ba5ff7c2c4d3327e444');

哇,现在有很多东西了:P所以我知道这些表格,特别是我称之为“imagebank”的“目录”可能看起来有点奇怪。但我有我的理由,这并不是真正的问题:)它是一个更大的图景的一部分。希望这能帮助你帮助我。再次感谢。

1 回复 | 直到 16 年前

1

mlennox 16 年前

看起来如果你把用户和目录id传给供应商就没关系了。

如果你在结果中需要供应商信息,那就另当别论了。

感觉你根本不应该让用户参与到这个查询中,因为你似乎在寻找某个特定供应商拥有的目录中的图像。

如果是这样的话,那么我将在查询中放弃对用户id的要求,而是使用供应商id。

我假设用户将执行以下操作,以达到启动此查询的目的:

登录-显然:)
点击“列出供应商”
单击供应商
单击目录

不管是哪种方式,您都需要做大量的内部连接

SELECT 
    s.supplier_id, 
    s.Supplier_name
FROM
    supplier s
INNER JOIN
    user u
INNER JOIN
    user2supplier u2s
ON
    u.user_id = u2s.user_id
ON
    u2s.supplier_id = s.supplier_id
WHERE
    u.user_id = 3 -- for example...

(现在,我还没有测试SQL,但我认为这是正确的…)

如果我在正确的轨道上,请告诉我-如果我帮了忙,如果可以的话,我很乐意再帮上忙