Professional Web Applications Themes

Need help ordering by the count of a join table in x number of hours - MySQL

I'm a bit stumped on this problem (definitely not a SQL guru). I have 2 tables like so: create table items ( id int unsigned not null auto_increment, user_id int unsigned not null, title varchar(64) not null, link varchar(128) not null, created_on timestamp, primary key (id) ) engine=innodb default cht=utf8; create table hits ( id int unsigned not null auto_increment, remote_ip varchar not null, item_id int unsigned not null, created_at datetime not null, primary key (id) ) engine=innodb default cht=utf8; How can I find all items ordered by the count of hits occuring within x number of hours? So, basically ...

  1. #1

    Default Need help ordering by the count of a join table in x number of hours

    I'm a bit stumped on this problem (definitely not a SQL guru). I have
    2 tables like so:

    create table items (
    id int unsigned not null auto_increment,
    user_id int unsigned not null,
    title varchar(64) not null,
    link varchar(128) not null,
    created_on timestamp,
    primary key (id)
    ) engine=innodb default cht=utf8;

    create table hits (
    id int unsigned not null auto_increment,
    remote_ip varchar not null,
    item_id int unsigned not null,
    created_at datetime not null,
    primary key (id)
    ) engine=innodb default cht=utf8;


    How can I find all items ordered by the count of hits occuring within
    x number of hours? So, basically I don't want a where clause. I want
    to retrieve *all* items. I just want them to be weighted by the
    number of hits they've received in the last hour or 6 hours or
    whatever...

    Thanks in advance

    wmwilson01@gmail.com Guest

  2. #2

    Default Re: Need help ordering by the count of a join table in x number of hours

    On Apr 1, 1:12 pm, com wrote: 

    I think you can do it this way:

    SELECT a.*
    FROM items a
    LEFT JOIN (

    SELECT DISTINCT (
    t1.item_id
    ), count( t1.item_id ) at_intervals, UNIX_TIMESTAMP( t1.created_at )
    FROM `hits` t1
    WHERE UNIX_TIMESTAMP( now( ) ) - UNIX_TIMESTAMP( t1.created_at ) <1200
    GROUP BY t1.item_id
    )b ON a.id = b.item_id
    ORDER BY at_intervals DESC , a.id;

    '1200' is the interval from NOW() in seconds. Just increase it to 3600
    for 1 hour etc.

    strawberry Guest

  3. #3

    Default Re: Need help ordering by the count of a join table in x number of hours

    On Apr 1, 9:45 am, "strawberry" <com> wrote: 




    >
    > I think you can do it this way:
    >
    > SELECT a.*
    > FROM items a
    > LEFT JOIN (
    >
    > SELECT DISTINCT (
    > t1.item_id
    > ), count( t1.item_id ) at_intervals, UNIX_TIMESTAMP( t1.created_at )
    > FROM `hits` t1
    > WHERE UNIX_TIMESTAMP( now( ) ) - UNIX_TIMESTAMP( t1.created_at ) <1200
    > GROUP BY t1.item_id
    > )b ON a.id = b.item_id
    > ORDER BY at_intervals DESC , a.id;
    >
    > '1200' is the interval from NOW() in seconds. Just increase it to 3600
    > for 1 hour etc.[/ref]


    That looks like a winner! Thanks so much for your help!

    wmwilson01@gmail.com Guest

  4. #4

    Default Re: Need help ordering by the count of a join table in x number of hours

    On Apr 1, 9:45 am, "strawberry" <com> wrote: 




    >
    > I think you can do it this way:
    >
    > SELECT a.*
    > FROM items a
    > LEFT JOIN (
    >
    > SELECT DISTINCT (
    > t1.item_id
    > ), count( t1.item_id ) at_intervals, UNIX_TIMESTAMP( t1.created_at )
    > FROM `hits` t1
    > WHERE UNIX_TIMESTAMP( now( ) ) - UNIX_TIMESTAMP( t1.created_at ) <1200
    > GROUP BY t1.item_id
    > )b ON a.id = b.item_id
    > ORDER BY at_intervals DESC , a.id;
    >
    > '1200' is the interval from NOW() in seconds. Just increase it to 3600
    > for 1 hour etc.[/ref]


    Well... I thought I would be able to modify this into a second case
    where I need this functionality, but I'm failing miserably. I have
    another table that's not dependent on getting the timestamp from a
    join, but again, I want to sort based on the count of items (grouped
    by the parent_item_id) where the created_at timestamp is within x
    number of hours.

    create table associations (
    id int unsigned not null auto_increment,
    user_id int unsigned not null,
    parent_item_id int unsigned not null,
    child_item_id int unsigned not null,
    created_at timestamp,
    primary key (id)
    ) engine=innodb default cht=utf8;

    Sorry that I still need help :).. I promise that the words "SQL
    wizard" will be nowhere close to my resume ;)

    wmwilson01@gmail.com Guest

  5. #5

    Default Re: Need help ordering by the count of a join table in x number of hours

    On Apr 1, 11:24 am, com wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]




    >
    > Well... I thought I would be able to modify this into a second case
    > where I need this functionality, but I'm failing miserably. I have
    > another table that's not dependent on getting the timestamp from a
    > join, but again, I want to sort based on the count of items (grouped
    > by the parent_item_id) where the created_at timestamp is within x
    > number of hours.
    >
    > create table associations (
    > id int unsigned not null auto_increment,
    > user_id int unsigned not null,
    > parent_item_id int unsigned not null,
    > child_item_id int unsigned not null,
    > created_at timestamp,
    > primary key (id)
    > ) engine=innodb default cht=utf8;
    >
    > Sorry that I still need help :).. I promise that the words "SQL
    > wizard" will be nowhere close to my resume ;)[/ref]


    As a quick note, it does appear that your original solution works for
    this case, I just have a gut feeling that that's ugly and inefficient
    (to select and left join to the same table)

    wmwilson01@gmail.com Guest

  6. #6

    Default Re: Need help ordering by the count of a join table in x number of hours

    On Apr 1, 4:27 pm, com wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]



    >
    > As a quick note, it does appear that your original solution works for
    > this case, I just have a gut feeling that that's ugly and inefficient
    > (to select and left join to the same table)[/ref]


    It's the subquery that's ugly and inefficient - not the self join!!!

    strawberry Guest

Similar Threads

  1. JOIN count query dilemma
    By Bosconian in forum MySQL
    Replies: 5
    Last Post: February 14th, 10:10 AM
  2. Create Table of Sequential Hours
    By stevensjn@gmail.com in forum MySQL
    Replies: 7
    Last Post: February 2nd, 07:09 PM
  3. Cross table ordering by
    By TommyMTL in forum Coldfusion Database Access
    Replies: 9
    Last Post: November 29th, 11:25 PM
  4. table ordering
    By patbegg in forum Dreamweaver AppDev
    Replies: 0
    Last Post: April 25th, 12:17 AM
  5. Tune a *simple* join on a COUNT()
    By Spendius in forum Oracle Server
    Replies: 3
    Last Post: November 20th, 03:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139