List the Top 25 machines on the home page

RyanTG commented 4 years ago

select coalesce(g.name, m.name), count(*) from location_machine_xrefs x inner join machines m on x.machine_id=m.id left outer join machine_groups g on m.machine_group_id = g.id group by 1 order by 2 desc limit 10;

The top 10 for each region code is here: https://github.com/scottwainstock/pbm/blob/master/app/views/pages/about.html.haml#L76

RyanTG commented 4 years ago

Logic from the top 10 is here: https://github.com/scottwainstock/pbm/blob/11eb2445f2a712d195ca0b0fb61a43940aa6a249/app/controllers/pages_controller.rb#L121

RyanTG commented 4 years ago

Ok, this works, and it's close to where I want to be:

    @top_machines = LocationMachineXref
      .includes(:machine)
      .select('machine_id, count(*) as machine_count')
      .group(:machine_id)
      .order('machine_count desc')
      .limit(25)

@scottwainstock Can you tell me how to group (or join?) machines by machine_group in the above definition? Like, if a machine_group exists, then use the machine_count for all the machines in that group.

scottwainstock commented 4 years ago

pbm_dev=# select coalesce(mg.name, m.name), count(*) count from location_machine_xrefs x inner join machines m on m.id=x.machine_id left outer join machine_groups mg on mg.id=m.machine_group_id group by 1 order by count desc limit 25;

      coalesce          | count

Let me try to turn this into activerecord. We'll need to tweak the machine_group names to have these look nicer.

scottwainstock commented 4 years ago

@RyanTG Maybe try this:

LocationMachineXref.joins(:machine).joins('LEFT OUTER JOIN machine_groups ON machine_groups.id = machines.id').select('coalesce(machine_groups.name, machines.name) as count_name, count(*) as machine_count').group(:count_name).order('machine_count desc').limit(25)

RyanTG commented 4 years ago

Thanks! I'll clean up the machine group names.

Once we add manufactuer and year fields, will this have to be tweaked in order to include those values in the result?

Also, just for my conscience, does this not combine the Stern Pirates of the Caribbean with the Jersey Jack Pirates of the Caribbean? There will be a couple cases like that. Jurassic Park is another one that has the same machine name but a different manufacturer.

RyanTG commented 4 years ago

Using the above, and this code to print it:

      - @top_machines.each do |lmx|
        .machine_quantity
          %p
            #{lmx.count_name}: with #{lmx.machine_count} machines

My results on local (I'm not using the most recent dump - but still it's not that told) don't seem to list any machine group names.


Ghostbusters (Pro): with 411 machines

Star Wars (Pro): with 336 machines

The Addams Family: with 328 machines

The Walking Dead (Pro): with 285 machines

Metallica (Pro): with 270 machines

The Simpsons Pinball Party: with 265 machines

Pirates of the Caribbean: with 263 machines

Star Trek (Pro): with 251 machines

Guardians of the Galaxy (Pro): with 229 machines

Lord of the Rings: with 221 machines

Game of Thrones (Pro): with 215 machines

Kiss: with 203 machines

Fish Tales: with 200 machines

Iron Maiden: Legacy of the Beast (Pro): with 193 machines

Aerosmith (Pro): with 192 machines

South Park: with 192 machines

Terminator 2: Judgment Day: with 191 machines

Spider-Man: with 179 machines

World Cup Soccer: with 173 machines

Twilight Zone: with 167 machines

Attack From Mars: with 157 machines

The Sopranos: with 157 machines

Theatre of Magic: with 153 machines

Funhouse: with 151 machines

Monopoly: with 148 machines

scottwainstock commented 4 years ago

@RyanTG I kind of bailed on the pure ActiveRecord approach and went with something like this: https://github.com/scottwainstock/pbm/blob/master/app/controllers/api/v1/location_machine_xrefs_controller.rb#L116 ... it might be worth just doing that here. It's not good form, but as long as we have tests exercising stuff, we can do a better version later.

RyanTG commented 2 years ago

fuck it.

RyanTG commented 2 years ago

Just for the record, I got the endpoint working for this https://github.com/pinballmap/pbm/issues/1212

pinballmap / pbm

List the Top 25 machines on the home page #1210