This is an issue that I've deemed impractical to implement but I would like to get some feedback to confirm.
这是一个我认为不切实际的问题,但我希望得到一些反馈来确认。
I have a product and users database, where users can like products, the like data is stored in a reference table with just pid and uid.
我有一个产品和用户数据库,用户可以在其中喜欢产品,类似的数据仅用pid和uid存储在一个引用表中。
The client request is to show 3 users who have liked every product in the product listing.
客户请求向3个用户显示他们喜欢产品列表中的所有产品。
The problem is, its not possible to get this data in one query for the product listing,
问题是,不可能在产品清单的一个查询中获得这些数据,
How I once implemented and subsequently un-implemented it was to perform a request for the users who have liked the products during the loop through the product list.
我曾经实现和后来未实现它的方式是,在产品列表的循环过程中为喜欢产品的用户执行一个请求。
ie.
ie。
foreach($prods as $row):
$likers = $this->model->get_likers($row->id);
endforeach;
That works, but obviously results in not only super slow product listings, and also creates a big strain on the database/cpu.
这是可行的,但是很明显,这不仅导致产品列表非常慢,而且还会对数据库/cpu造成很大的压力。
The final solution that was implemented was to only show the latest user who has liked it (this can be gotten from a join in the products list query) and have a link showing how many people have liked, and upon clicking on it, opens a ajax list of likers.
实现的最后一个解决方案是只显示最新的喜欢它的用户(这可以从product list查询的一个连接中获得),并有一个显示有多少人喜欢它的链接,单击该链接,就会打开一个ajax likers列表。
So my question is, is there actually a technique to show likers on the product list, or is it simply not possible to execute practically? I notice actually for most social media sites, they do not show all likers on the listings, and do employ the 'click to see likers' method. However, they do show comments per items on the listing, and this is actually involves the same problem doesn't it?
所以我的问题是,是否有一种技术可以在产品列表中显示相似的人,或者它仅仅是不可能实际执行?我注意到,对于大多数社交媒体网站来说,它们不会显示列表上的所有喜欢的人,而是使用“点击查看喜欢的人”的方法。但是,它们确实显示了清单上的每个条目的注释,这实际上包含了相同的问题,不是吗?
Any help/guidance would be greatly appreciated!
非常感谢您的帮助和指导!
Thanks
谢谢
Edit: mock up attached on the desired outcome. there would be 30 products per page.
编辑:模拟附加在期望的结果。每页将有30种产品。
4 个解决方案
#1
3
By reading your comment reply to Alex.Ritna ,yes you can get the x no. of results with per group ,using GROUP_CONCAT()
and the SUBSTRING_INDEX()
it will show the likers seperated by comma or whatever separator you specified in the query (i have used ||).ORDER BY
clause can be used in group_concat function.As there is no schema information is available so i assume you have one product table one user table and a junction table that maintains the relation of user and product.In the substring function i have used x=3
通过阅读你对Alex的评论回复。Ritna,是的,你可以得到x不。对于每个组的结果,使用GROUP_CONCAT()和SUBSTRING_INDEX(),它将显示用逗号或查询中指定的任何分隔符分隔的likers(我使用了||)。group_concat函数中可以使用ORDER BY子句。由于没有可用的模式信息,所以我假设您有一个产品表、一个用户表和一个连接表,它们维护用户和产品的关系。在子串函数中,我用了x=3。
SELECT p.*,
COUNT(*) total_likes,
SUBSTRING_INDEX(
GROUP_CONCAT( CONCAT(u.firstname,' ',u.lastname) ORDER BY some_column DESC SEPARATOR '||'),
'||',3) x_no_of_likers
FROM product p
LEFT JOIN junction_table jt ON(p.id=jt.product_id)
INNER JOIN users u ON(u.id=jt.user_id)
GROUP BY p.id
Fiddle
Now at your application level you just have to loop through the products and split the x_no_of_likers
by separator you the likers per product
现在在应用程序级别,您只需循环遍历这些产品,并将x_no_of_likers分隔为每个产品的likers。
foreach($prods as $row):
$likers=explode('||',$row['x_no_of_likers']);
$total_likes= $row['total_likes'];
foreach($likers as $user):
....
endforeach;
endforeach;
Note there is a default 1024 character limit set on GROUP_CONCAT()
but you can also increase it by following the GROUP_CONCAT() manual
注意GROUP_CONCAT()上有一个默认的1024字符限制,但是您也可以通过遵循GROUP_CONCAT()手册来增加它
Edit from comments This is another way how to get n results per group, from this you can get all the fields from your user table i have used some variables to get the rank for product group ,used subquery for junction_table
to get the rank and in outer select i have filtered records with this rank using HAVING jt.user_rank <=3
so it will give three users records per product ,i have also used subquery for products (SELECT * FROM product LIMIT 30 )
so the first 30 groups will have 3 results for each,for below query limit cannot be used at the end so i have used in the subquery
编辑从评论这是另一种如何获得n每组结果,从这个你可以从你的用户表的所有字段我用一些变量得到产品组的排名,子查询用于junction_table秩和在外部选择我有过滤记录等级使用jt。user_rank < = 3所以它会给三个用户记录每个产品,我也使用子查询产品(从产品限制选择* 30)第一个30组3的结果,下面的查询限制不能使用在子查询中使用
SELECT p.id,p.title,u.firstname,u.lastname,u.thumbnail,jt.user_rank
FROM
(SELECT * FROM `product` LIMIT 30 ) p
LEFT JOIN
( SELECT j.*,
@current_rank:= CASE WHEN @current_rank = product_id THEN @user_rank:=@user_rank +1 ELSE @user_rank:=1 END user_rank,
@current_rank:=product_id
FROM `junction_table` j ,
(SELECT @user_rank:=0,@current_rank:=0) r
ORDER BY product_id
) jt ON(jt.product_id = p.id)
LEFT JOIN `users` u ON (jt.`user_id` = u.`id`)
HAVING jt.user_rank <=3
ORDER BY p.id
Fiddle n results per group
#2
1
You should be able to get a list of all users that have liked all products with this sql.
您应该能够获得使用此sql的所有产品的所有用户的列表。
select uid,
count(pid) as liked_products
from product_user
group by uid
having liked_products = (select count(1) from products);
But as data grows this query gets slow. Better then to maintain a table with like counts that is maintained through a trigger or separately. On every like/dislike the counter is updated. This makes it easy to show the number of likes for each product. Then if the actual users that liked that product is wanted do a separate call (on user interaction) that fetches the specific likes for one product). Don't do this for all products on a page until actually requested.
但是随着数据的增长,这个查询变得越来越慢。相比之下,最好是通过触发器或单独维护具有类似计数的表。在每一个喜欢/不喜欢的地方,柜台都会更新。这样就可以很容易地显示每个产品的赞数。然后,如果真正喜欢该产品的用户需要做一个单独的调用(在用户交互上),获取一个产品的特定喜好)。在实际请求之前,不要对页面上的所有产品都这样做。
#3
0
I am assuming the size of both these tables is non-trivially large. You should create a new table (say LastThreeLikes
), where the columns would be pid
,uid_1
,uid_2
and uid_3
, indexed by pid
. Also, add a column to your product table called numLikes
.
我假设这两个表的大小都不是非常大。您应该创建一个新的表(比如lastthreelike),其中的列将是pid、uid_1、uid_2和uid_3,由pid索引。另外,向产品表中添加一个名为numLikes的列。
For each "like" that you enter into your reference table, create a trigger that also populates this LastThreeLikes
table if the numLikes
is less than 3. You can choose to randomly update one of the values anyway if you want to show new users once in a while.
对于您在引用表中输入的每个“like”,如果numLikes小于3,则创建一个触发器来填充这个lastthreelike表。如果您希望偶尔显示新用户,您可以选择随机更新其中一个值。
While displaying a product, simply fetch the uid
s from this table and display them back.
在显示产品时,只需从该表中获取uid并将其显示回来。
Note that you also need to maintain a trigger for the "Unlike" action (if there is any) to re-populate the LastThreeLikes
table with a new user id.
注意,您还需要为“不同”操作(如果有的话)维护一个触发器,以便使用新的用户id重新填充lastthreelike表。
#4
0
Problem
问题
The problem is the volume of data. From the point of view that you need two integer value as a answer you should forget about building a heavy query from your n<->n relations table.
问题在于数据量。从您需要两个整数值作为答案的角度来看,您应该忘记从您的n<->n关系表构建一个大查询。
Solution
解决方案
Generates a storable representation using the file_put_contents() with append option each time a user likes a product. I don't have enough room to write the class in here.
每次用户喜欢一个产品时,使用file_put_contents()和append选项生成一个可存储的表示。我没有足够的空间在这里写课。
public function export($file);
3D array format
三维数组的格式
array[product][line][user]
Example:
例子:
$likes[1293][1][456]=1;
$likes[82][2][656]=1;
$likes[65][3][456]=1;
.
.
.
Number of users who like this particular product:
喜欢本产品的用户数量:
$number_users_like_this_product = count($likes[$idProduct]);
All idUser who like this particular product:
所有喜欢这个产品的用户:
$users_like_this_product = count($likes[$idProduct][$n]);
All likes
所有喜欢
$all_likes = count($likes);
Deleting a like
删除一个像
This loop will unset the only line where $idProduct and $IdUser you want. Since all the variables are unsigned integer it is very fast.
这个循环将解除$idProduct和$IdUser所需的唯一行。因为所有的变量都是无符号整数,所以它是非常快的。
for($n=1, $n <= count($likes[$idProduct]), $n++)
{
unset($likes[$idProduct][$n][$idUser]);
}
Conclusion
结论
Get all likes will be easy as:
让所有的喜欢都是容易的:
include('likes.php');
P.S If you want to give a try i will be glad to optimize my stuff and share it. I've created the class in 2012.
P。如果你想尝试一下,我很乐意优化我的东西并与你分享。我在2012年开设了这门课。
#1
3
By reading your comment reply to Alex.Ritna ,yes you can get the x no. of results with per group ,using GROUP_CONCAT()
and the SUBSTRING_INDEX()
it will show the likers seperated by comma or whatever separator you specified in the query (i have used ||).ORDER BY
clause can be used in group_concat function.As there is no schema information is available so i assume you have one product table one user table and a junction table that maintains the relation of user and product.In the substring function i have used x=3
通过阅读你对Alex的评论回复。Ritna,是的,你可以得到x不。对于每个组的结果,使用GROUP_CONCAT()和SUBSTRING_INDEX(),它将显示用逗号或查询中指定的任何分隔符分隔的likers(我使用了||)。group_concat函数中可以使用ORDER BY子句。由于没有可用的模式信息,所以我假设您有一个产品表、一个用户表和一个连接表,它们维护用户和产品的关系。在子串函数中,我用了x=3。
SELECT p.*,
COUNT(*) total_likes,
SUBSTRING_INDEX(
GROUP_CONCAT( CONCAT(u.firstname,' ',u.lastname) ORDER BY some_column DESC SEPARATOR '||'),
'||',3) x_no_of_likers
FROM product p
LEFT JOIN junction_table jt ON(p.id=jt.product_id)
INNER JOIN users u ON(u.id=jt.user_id)
GROUP BY p.id
Fiddle
Now at your application level you just have to loop through the products and split the x_no_of_likers
by separator you the likers per product
现在在应用程序级别,您只需循环遍历这些产品,并将x_no_of_likers分隔为每个产品的likers。
foreach($prods as $row):
$likers=explode('||',$row['x_no_of_likers']);
$total_likes= $row['total_likes'];
foreach($likers as $user):
....
endforeach;
endforeach;
Note there is a default 1024 character limit set on GROUP_CONCAT()
but you can also increase it by following the GROUP_CONCAT() manual
注意GROUP_CONCAT()上有一个默认的1024字符限制,但是您也可以通过遵循GROUP_CONCAT()手册来增加它
Edit from comments This is another way how to get n results per group, from this you can get all the fields from your user table i have used some variables to get the rank for product group ,used subquery for junction_table
to get the rank and in outer select i have filtered records with this rank using HAVING jt.user_rank <=3
so it will give three users records per product ,i have also used subquery for products (SELECT * FROM product LIMIT 30 )
so the first 30 groups will have 3 results for each,for below query limit cannot be used at the end so i have used in the subquery
编辑从评论这是另一种如何获得n每组结果,从这个你可以从你的用户表的所有字段我用一些变量得到产品组的排名,子查询用于junction_table秩和在外部选择我有过滤记录等级使用jt。user_rank < = 3所以它会给三个用户记录每个产品,我也使用子查询产品(从产品限制选择* 30)第一个30组3的结果,下面的查询限制不能使用在子查询中使用
SELECT p.id,p.title,u.firstname,u.lastname,u.thumbnail,jt.user_rank
FROM
(SELECT * FROM `product` LIMIT 30 ) p
LEFT JOIN
( SELECT j.*,
@current_rank:= CASE WHEN @current_rank = product_id THEN @user_rank:=@user_rank +1 ELSE @user_rank:=1 END user_rank,
@current_rank:=product_id
FROM `junction_table` j ,
(SELECT @user_rank:=0,@current_rank:=0) r
ORDER BY product_id
) jt ON(jt.product_id = p.id)
LEFT JOIN `users` u ON (jt.`user_id` = u.`id`)
HAVING jt.user_rank <=3
ORDER BY p.id
Fiddle n results per group
#2
1
You should be able to get a list of all users that have liked all products with this sql.
您应该能够获得使用此sql的所有产品的所有用户的列表。
select uid,
count(pid) as liked_products
from product_user
group by uid
having liked_products = (select count(1) from products);
But as data grows this query gets slow. Better then to maintain a table with like counts that is maintained through a trigger or separately. On every like/dislike the counter is updated. This makes it easy to show the number of likes for each product. Then if the actual users that liked that product is wanted do a separate call (on user interaction) that fetches the specific likes for one product). Don't do this for all products on a page until actually requested.
但是随着数据的增长,这个查询变得越来越慢。相比之下,最好是通过触发器或单独维护具有类似计数的表。在每一个喜欢/不喜欢的地方,柜台都会更新。这样就可以很容易地显示每个产品的赞数。然后,如果真正喜欢该产品的用户需要做一个单独的调用(在用户交互上),获取一个产品的特定喜好)。在实际请求之前,不要对页面上的所有产品都这样做。
#3
0
I am assuming the size of both these tables is non-trivially large. You should create a new table (say LastThreeLikes
), where the columns would be pid
,uid_1
,uid_2
and uid_3
, indexed by pid
. Also, add a column to your product table called numLikes
.
我假设这两个表的大小都不是非常大。您应该创建一个新的表(比如lastthreelike),其中的列将是pid、uid_1、uid_2和uid_3,由pid索引。另外,向产品表中添加一个名为numLikes的列。
For each "like" that you enter into your reference table, create a trigger that also populates this LastThreeLikes
table if the numLikes
is less than 3. You can choose to randomly update one of the values anyway if you want to show new users once in a while.
对于您在引用表中输入的每个“like”,如果numLikes小于3,则创建一个触发器来填充这个lastthreelike表。如果您希望偶尔显示新用户,您可以选择随机更新其中一个值。
While displaying a product, simply fetch the uid
s from this table and display them back.
在显示产品时,只需从该表中获取uid并将其显示回来。
Note that you also need to maintain a trigger for the "Unlike" action (if there is any) to re-populate the LastThreeLikes
table with a new user id.
注意,您还需要为“不同”操作(如果有的话)维护一个触发器,以便使用新的用户id重新填充lastthreelike表。
#4
0
Problem
问题
The problem is the volume of data. From the point of view that you need two integer value as a answer you should forget about building a heavy query from your n<->n relations table.
问题在于数据量。从您需要两个整数值作为答案的角度来看,您应该忘记从您的n<->n关系表构建一个大查询。
Solution
解决方案
Generates a storable representation using the file_put_contents() with append option each time a user likes a product. I don't have enough room to write the class in here.
每次用户喜欢一个产品时,使用file_put_contents()和append选项生成一个可存储的表示。我没有足够的空间在这里写课。
public function export($file);
3D array format
三维数组的格式
array[product][line][user]
Example:
例子:
$likes[1293][1][456]=1;
$likes[82][2][656]=1;
$likes[65][3][456]=1;
.
.
.
Number of users who like this particular product:
喜欢本产品的用户数量:
$number_users_like_this_product = count($likes[$idProduct]);
All idUser who like this particular product:
所有喜欢这个产品的用户:
$users_like_this_product = count($likes[$idProduct][$n]);
All likes
所有喜欢
$all_likes = count($likes);
Deleting a like
删除一个像
This loop will unset the only line where $idProduct and $IdUser you want. Since all the variables are unsigned integer it is very fast.
这个循环将解除$idProduct和$IdUser所需的唯一行。因为所有的变量都是无符号整数,所以它是非常快的。
for($n=1, $n <= count($likes[$idProduct]), $n++)
{
unset($likes[$idProduct][$n][$idUser]);
}
Conclusion
结论
Get all likes will be easy as:
让所有的喜欢都是容易的:
include('likes.php');
P.S If you want to give a try i will be glad to optimize my stuff and share it. I've created the class in 2012.
P。如果你想尝试一下,我很乐意优化我的东西并与你分享。我在2012年开设了这门课。