Forum d'entraide PHPFrance

Bonjour,

Je dois effectuer ce genre de requête pour obtenir ma liste de vidéos avec tous ses paramètres réunis, car sans vue je ne peux pas faire le tri à cause du GROUP BY:

CREATE VIEW videos_view AS 
            SELECT `videos`.*, `category_langue`.`name`, `category_langue`.`slug`, SUM(videos_vues.nbre_vues) AS nbre_vues FROM `videos` 
            INNER JOIN `videos_vues` ON `videos_vues`.`video_id` = `videos`.`id` 
            INNER JOIN `categories` ON `categories`.`id` = `videos`.`category_id` 
            INNER JOIN `category_langue` ON `categories`.`id` = `category_langue`.`category_id` 
            INNER JOIN `langues` ON `langues`.`id` = `category_langue`.`langue_id` 
            WHERE `langues`.`iso` = "fr" 
                GROUP BY `videos`.`id` ORDER BY `videos`.`id` DESC'

mais étant donné que j'ai plusieurs langues, qu'il puisse s'agir d'une catégorie précise, ou même d'une recherche précise avec LIKE etc.. je ne peux donc pas créer autant de vues qu'il y a de possibilités..

Ma question étant donc est ce possible de rajouter un IF EXISTS DROP devant pour la créer/supprimer à la volée ? et permettre à chaque fois d'avoir tous mes paramètres dynamiques.

Et que ce passera t'il si un visiteur execute la page en même temps que d'autres, vu qu'il y a suppression de la vue, cela va t'il générer une erreur pour les accès en même temps ?

Et éventuellement s'il existe une solution alternative, je suis bien entendu ouvert

Merci

salut,

pourquoi serait tu obligé d'utiliser une vue ?

Ta requête n'a rien d'extra ordinaire.

Passe en paramètre la langue et c'est réglé.

tu peux aussi virer les ` qui sont inutile (ça sert juste a protéger les champs si tu à la mauvaise idée d'utiliser des mots clef sql dans les nom de champs ou table, ce qui est déconseillé ).


@+

A cause du GROUP BY, les vidéos ne sont pas bien triés par nbre_vues, car il semble faire le tri avant de grouper, donc la vue m'aurait permis de faire sans soucis un order by sur le résultat de ma requete.

ta requete ordonne sur l'id et pas le sum

tu peux utiliser la clause having pour effectuer un traitement après le reste.

mais la vue reste une très mauvaise idée, coté perf et coté fonctionnement.

qu'as tu essayé comme requete ?

peux tu poster le ddl (create table) plus un jeux de donnée pour test ?

@+

ta requete ordonne sur l'id et pas le sum

tu peux utiliser la clause having pour effectuer un traitement après le reste.

mais la vue reste une très mauvaise idée, coté perf et coté fonctionnement.

qu'as tu essayé comme requete ?

peux tu poster le ddl (create table) plus un jeux de donnée pour test ?

@+

Effectivement celle ci contient l'ordre par videos id DESC, je n'ai plus le code source sous les yeux, mais je vérifierai demain si j'ai pris la mauvaise requête ou si j'avais cette erreur que je ne voyais pas.

Coté perf, j'ai vu ça je suis passé de 0.015 sec à 0,400 environ en pointe, j'essayai de me rassurer en pensant que j'allais avoir la requête en cache.

Pour les bases présente dans la req et un jeu de donnée, voila le sql

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

INSERT INTO `categories` (`id`, `status`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 1),
(7, 1),
(8, 1);

CREATE TABLE IF NOT EXISTS `category_langue` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `image` varchar(255) NOT NULL,
  `langue_id` int(10) unsigned NOT NULL,
  `category_id` int(10) unsigned NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

INSERT INTO `category_langue` (`id`, `name`, `description`, `slug`, `image`, `langue_id`, `category_id`, `created_at`, `updated_at`) VALUES
(1, 'Street', 'blabalbalba Street', 'street', '', 2, 1, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(2, 'Crashs', 'blabalbalba Crashs', 'crashs', '', 2, 2, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(3, 'Brakeless', 'blabalbalba Brakeless', 'brakeless', '', 2, 3, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(4, 'Compétitions', 'blabalbalba Compétitions', 'competitions', '', 2, 4, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(5, 'Shows', 'blabalbalba Shows', 'shows', '', 2, 5, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(6, 'Pubs', 'blabalbalba Pubs', 'pubs', '', 2, 6, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(7, 'Interviews', 'blabalbalba Interviews', 'interviews', '', 2, 7, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(8, 'Tutos', 'blabalbalba Tutos', 'tutos', '', 2, 8, '0000-00-00 00:00:00', '0000-00-00 00:00:00');

CREATE TABLE IF NOT EXISTS `langues` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `iso` varchar(2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `langues_iso_index` (`iso`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `langues` (`id`, `name`, `iso`) VALUES
(1, 'Anglais', 'en'),
(2, 'Français', 'fr'),
(3, 'Allemand', 'de'),
(4, 'Espagnol', 'es'),
(5, 'Italien', 'it');

CREATE TABLE IF NOT EXISTS `videos` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `video_id` varchar(100) NOT NULL,
  `video_site` varchar(100) NOT NULL,
  `description` text NOT NULL,
  `image` varchar(255) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `category_id` int(10) unsigned NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `videos_category_id_index` (`category_id`),
  FULLTEXT KEY `title` (`title`,`description`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;

INSERT INTO `videos` (`id`, `title`, `video_id`, `video_site`, `description`, `image`, `status`, `category_id`, `created_at`, `updated_at`) VALUES
(1, 'Inspired Bicycles - Danny MacAskill April 2009', 'Z19zFlPah-o', 'youtube', 'Filmed over the period of a few months in and around Edinburgh by Dave Sowerby, this video of Inspired Bicycles team rider Danny MacAskill (more info at http://www.dannymacaskill.com) features probably the best collection of street/street trials riding ever seen. There''s some huge riding, but also some of the most technically difficult and imaginative lines you will ever see. Without a doubt, this video pushes the envelope of what is perceived as possible on a trials bike.\r\n\r\nCredit to Band of Horses for their epic song ''The Funeral.'' You can find out more about the band and their music at http://www.bandofhorses.com or you can buy the featured song from itunes here: http://ax.itunes.apple.com/WebObjects/MZStore.woa/wa/browserRedirect?url=itms%253A%252F%252Fax.itunes.apple.com%252FWebObjects%252FMZStore.woa%252Fwa%252FviewAlbum%253Fi%253D129649334%2526id%253D129649178%2526s%253D143441', 'http://i.ytimg.com/vi/Z19zFlPah-o/hqdefault.jpg', 1, 1, '2013-01-30 17:11:14', '2013-01-30 17:11:14'),
(7, 'Danny Macaskill: Industrial Revolutions', '30043673', 'vimeo', 'Industrial Revolutions is the amazing new film from street trials riding star Danny Macaskill. Filmed and edited for Channel 4 ''s documentary Concrete Circus.\r\n\r\nIndustrial Revolutions sees Danny take his incredible bike skills into an industrial train yard and some derelict buildings.'' Filmed in the beautiful Scottish countryside Danny Macaskill''s latest film was directed by Stu Thomson (Cut Media/MTBcut) for Channel 4''s documentary Concrete Circus.\r\n\r\nwww.CutMedia.com\r\nwww.DannyMacaskill.com\r\n\r\nProduction team : Renegade Pictures\r\nThomas Scott Evans, Eddie Sears, Sunita Mirchandani\r\nLine Producer : Renegade Pictures\r\nRachel Naughton\r\nProducer : Mike Christie\r\nDirected by: Stu Thomson\r\nFilmed by:  Stu Thomson, Aaron Bartlett\r\n\r\nMusic is ''The Wolves'' by Ben Howard courtesy of Universal Island Records', 'http://b.vimeocdn.com/ts/201/594/201594439_640.jpg', 1, 1, '2013-01-31 16:00:19', '2013-01-31 16:00:19'),
(3, 'TRA 386', '12903110', 'vimeo', 'Hi, i put on a half link chain on my bike so the chainstay turned out at 386 instead of 392 , went out to test , but the day suddenly got halted fairly quickly.\r\n\r\nTrials rims aint ment to last for 2 years .. :P', 'http://b.vimeocdn.com/ts/731/839/73183988_640.jpg', 1, 3, '2013-01-30 17:51:09', '2013-01-30 17:51:09'),
(4, 'Danny MacAskill April 2009', '4291982', 'vimeo', 'Danny MacAskill April 2009', 'http://b.vimeocdn.com/ts/967/390/9673907_640.jpg', 1, 8, '2013-01-31 13:38:30', '2013-01-31 13:38:30'),
(8, 'Danny Macaskill & Ryan Leech at Crankworx 2011', '30924695', 'vimeo', 'Danny Macaskill and Ryan Leech perform together at Crankworx 2011. I was super stoked to see this show. I''ve seen Ryan before, but never had a chance to see Danny till this day. I hope you enjoy my video.', 'http://b.vimeocdn.com/ts/208/019/208019264_640.jpg', 1, 5, '2013-01-31 16:05:50', '2013-01-31 16:05:50'),
(9, 'Danny MacAskill Plays Capetown: The Making of', '24120283', 'vimeo', 'After exploring a new playground on his bike, Danny takes a break for an interview to share his experience about riding through Cape Town and what he thinks about the new Leica V-Lux 30. See his 360 ° jumps and other pro trials riding in one of the most beautiful cities in the world: bit.ly/?goplayLeica.\r\n\r\nFor more information on the new Leica V-Lux 30, visit http://www.v-lux30.leica-camera.com and go play!', 'http://b.vimeocdn.com/ts/157/682/157682868_640.jpg', 1, 7, '2013-01-31 16:06:20', '2013-01-31 16:06:20'),
(10, 'Kenny Belaey - Los Angeles 2011', '19906769', 'vimeo', 'Kenny on his first ride of a 24" street bike...Just having fun in Los Angeles when he was out for an Adidas commercial back in December.\r\n\r\n\r\nSong: Twin Shadow - Tether Beat\r\nLocations: UCLA, Beverly Hills + downtown Los Angeles.', 'http://b.vimeocdn.com/ts/126/651/126651739_640.jpg', 1, 6, '2013-01-31 16:11:10', '2013-01-31 16:11:10'),
(11, 'Danny MacAskill, S1Jobs.com - Behind the Scenes', '6598175', 'vimeo', 'Behind the scenes filming the S1jobs.com advert with Danny MacAskill.', 'http://b.vimeocdn.com/ts/254/945/25494540_640.jpg', 1, 1, '2013-01-31 16:12:29', '2013-02-04 16:00:49'),
(13, 'Inspired Bicycles - Danny MacAskill April 2009', 'x9m3i8', 'dailymotion', 'Inspired bicycles danny macaskill !\r\nThe best of the best rider of dirt  and street.', 'http://s1.dmcdn.net/_Uhe.jpg', 1, 2, '2013-02-04 15:47:08', '2013-02-04 16:04:15'),
(14, 'Inspired Bicycles - Danny MacAskill April 2009', 'Z19zFlPah-o', 'youtube', 'Filmed over the period of a few months in and around Edinburgh by Dave Sowerby, this video of Inspired Bicycles team rider Danny MacAskill (more info at http://www.dannymacaskill.com) features probably the best collection of street/street trials riding ever seen. There''s some huge riding, but also some of the most technically difficult and imaginative lines you will ever see. Without a doubt, this video pushes the envelope of what is perceived as possible on a trials bike.\r\n\r\nCredit to Band of Horses for their epic song ''The Funeral.'' You can find out more about the band and their music at http://www.bandofhorses.com or you can buy the featured song from itunes here: http://ax.itunes.apple.com/WebObjects/MZStore.woa/wa/browserRedirect?url=itms%253A%252F%252Fax.itunes.apple.com%252FWebObjects%252FMZStore.woa%252Fwa%252FviewAlbum%253Fi%253D129649334%2526id%253D129649178%2526s%253D143441', 'http://i.ytimg.com/vi/Z19zFlPah-o/hqdefault.jpg', 1, 3, '2013-02-07 15:05:37', '2013-02-07 15:05:37'),
(15, 'Inspired Bicycles - Danny MacAskill April 2009', 'Z19zFlPah-o', 'youtube', 'Filmed over the period of a few months in and around Edinburgh by Dave Sowerby, this video of Inspired Bicycles team rider Danny MacAskill (more info at http://www.dannymacaskill.com) features probably the best collection of street/street trials riding ever seen. There''s some huge riding, but also some of the most technically difficult and imaginative lines you will ever see. Without a doubt, this video pushes the envelope of what is perceived as possible on a trials bike.\r\n\r\nCredit to Band of Horses for their epic song ''The Funeral.'' You can find out more about the band and their music at http://www.bandofhorses.com or you can buy the featured song from itunes here: http://ax.itunes.apple.com/WebObjects/MZStore.woa/wa/browserRedirect?url=itms%253A%252F%252Fax.itunes.apple.com%252FWebObjects%252FMZStore.woa%252Fwa%252FviewAlbum%253Fi%253D129649334%2526id%253D129649178%2526s%253D143441', 'http://i.ytimg.com/vi/Z19zFlPah-o/hqdefault.jpg', 1, 7, '2013-02-07 15:05:44', '2013-02-07 15:05:44'),
(16, 'Inspired Bicycles - Danny MacAskill April 2009', 'Z19zFlPah-o', 'youtube', 'Filmed over the period of a few months in and around Edinburgh by Dave Sowerby, this video of Inspired Bicycles team rider Danny MacAskill (more info at http://www.dannymacaskill.com) features probably the best collection of street/street trials riding ever seen. There''s some huge riding, but also some of the most technically difficult and imaginative lines you will ever see. Without a doubt, this video pushes the envelope of what is perceived as possible on a trials bike.\r\n\r\nCredit to Band of Horses for their epic song ''The Funeral.'' You can find out more about the band and their music at http://www.bandofhorses.com or you can buy the featured song from itunes here: http://ax.itunes.apple.com/WebObjects/MZStore.woa/wa/browserRedirect?url=itms%253A%252F%252Fax.itunes.apple.com%252FWebObjects%252FMZStore.woa%252Fwa%252FviewAlbum%253Fi%253D129649334%2526id%253D129649178%2526s%253D143441', 'http://i.ytimg.com/vi/Z19zFlPah-o/hqdefault.jpg', 1, 5, '2013-02-07 15:05:52', '2013-02-07 15:05:52'),
(19, 'Chris Akrigg  THE MAURICE.0', '9824201', 'vimeo', 'So here i go again. This is my second attempt at riding the Fixed gear bike ( the Mongoose Maurice) . Filmed on a recent trip to Barcelona. The last time i rode this bike was across the horse at the end of the last vid 6 months ago...enjoy', 'http://b.vimeocdn.com/ts/497/531/49753135_640.jpg', 1, 3, '2013-02-07 17:58:29', '2013-02-07 17:58:29'),
(18, 'A Hill in Spain', '20601448', 'vimeo', 'Filmed on a recent trip to Malaga aboard the Mongoose Nugget. Had a lot fun putting this together, hope you enjoy..\r\nJe ne sais pas dans quoi le mettre ^^', 'http://b.vimeocdn.com/ts/132/213/132213672_640.jpg', 1, 1, '2013-02-07 17:55:16', '2013-02-07 17:55:16'),
(20, 'Chris Akrigg''s Day at the Seaside', '3294280', 'vimeo', 'Clue is in the title !', 'http://b.vimeocdn.com/ts/182/774/1827742_640.jpg', 1, 3, '2013-02-07 18:08:53', '2013-02-07 18:08:53'),
(21, 'Val Isere Gilles Coustellier s''impose sous la pluie', 'PqKeOki-mIA', 'youtube', 'la description :)', 'http://i.ytimg.com/vi/PqKeOki-mIA/hqdefault.jpg', 1, 4, '2013-02-07 18:09:24', '2013-02-07 18:09:24'),
(22, 'Marc Caisso', 'x8ojtc', 'dailymotion', 'Entrainement de Marc Caisso sur ses terres.  Spots trial sur la vidéo :  St restitut, Digues de Sète, et Village de Fabrègues.  Retrouvez toute ses vidéos sur http://www.marc-caisso.com  ', 'http://s2.dmcdn.net/AeCyG.jpg', 1, 5, '2013-02-07 18:09:47', '2013-02-07 18:09:47');

CREATE TABLE IF NOT EXISTS `videos_vues` (
  `video_id` int(10) unsigned NOT NULL,
  `nbre_vues` int(10) unsigned NOT NULL,
  `added_at` datetime NOT NULL,
  PRIMARY KEY (`video_id`,`added_at`),
  KEY `videos_vues_video_id_index` (`video_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `videos_vues` (`video_id`, `nbre_vues`, `added_at`) VALUES
(10, 28, '2013-02-01 00:00:00'),
(1, 19, '2013-02-01 00:00:00'),
(7, 115, '2013-02-01 00:00:00'),
(3, 5, '2013-02-01 00:00:00'),
(8, 4, '2013-02-01 00:00:00'),
(11, 17, '2013-02-01 00:00:00'),
(9, 45, '2013-02-01 00:00:00'),
(4, 37, '2013-02-01 00:00:00'),
(7, 4, '2013-02-04 00:00:00'),
(7, 17, '2013-02-02 00:00:00'),
(9, 3, '2013-02-02 00:00:00'),
(4, 2, '2013-02-02 00:00:00'),
(11, 5, '2013-02-02 00:00:00'),
(1, 119, '2013-02-02 00:00:00'),
(10, 22, '2013-02-02 00:00:00'),
(8, 8, '2013-02-02 00:00:00'),
(3, 6, '2013-02-02 00:00:00'),
(1, 86, '2013-02-04 00:00:00'),
(7, 1, '2013-02-03 00:00:00'),
(11, 2, '2013-02-03 00:00:00'),
(1, 1, '2013-02-03 00:00:00'),
(10, 2, '2013-02-04 00:00:00'),
(9, 1, '2013-02-04 00:00:00'),
(3, 21, '2013-02-04 00:00:00'),
(11, 19, '2013-02-04 00:00:00'),
(13, 7, '2013-02-04 00:00:00'),
(4, 1, '2013-02-06 00:00:00'),
(7, 1, '2013-02-06 00:00:00'),
(10, 2, '2013-02-06 00:00:00'),
(13, 1, '2013-02-07 00:00:00'),
(4, 9, '2013-02-07 00:00:00'),
(14, 1, '2013-02-07 00:00:00'),
(15, 1, '2013-02-07 00:00:00'),
(16, 3, '2013-02-07 00:00:00'),
(19, 2, '2013-02-07 00:00:00'),
(9, 1, '2013-02-07 00:00:00'),
(7, 3, '2013-02-07 00:00:00'),
(3, 1, '2013-02-07 00:00:00'),
(10, 2, '2013-02-07 00:00:00'),
(8, 1, '2013-02-07 00:00:00'),
(1, 1, '2013-02-07 00:00:00'),
(18, 2, '2013-02-07 00:00:00'),
(20, 2, '2013-02-07 00:00:00'),
(21, 1, '2013-02-07 00:00:00'),
(22, 3, '2013-02-07 00:00:00');

Le primary id dans la table intermédiaire n'est pas de ma volonté, j'utilise un ORM et il m'a un peu forcé la main

si tu met le nombre de vue sur l'order by tu as bien les données ordonnées par le nombre de vue

La requete pour le tri que tu souhaite, ordonnée par nombre de vue par ordre décroissant et par id vidéo par ordre croissant (déterminant sur les exaequo );

SELECT videos.*, category_langue.name, category_langue.slug, SUM(videos_vues.nbre_vues) AS nbre_vues FROM videos
INNER JOIN videos_vues ON videos_vues.video_id = videos.id
INNER JOIN categories ON categories.id = videos.category_id
INNER JOIN category_langue ON categories.id = category_langue.category_id
INNER JOIN langues ON langues.id = category_langue.langue_id
WHERE langues.iso = 'fr'
GROUP BY videos.id ORDER BY nbre_vues DESC, videos.id ASC;

@+

Il me semble me souvenir que l'ordre qui n'était pas tjrs respecté quand j'avais une filtrage par mois par exemple (WHERE MONTH(videos_vues.added_at ) = MONTH( CURDATE() ) )

Car il ordonnait par videos_vues.nbre_vues avant de grouper par video_id , et si dans ce cas de figure le nombre de vues associé a cette video_id pour un seul jour était inférieur, il se basait donc sur celui la, meme si le SUM était par après différent.

Je pense que je ne suis pas très clair, mais ca va être difficile de faire mieux

Merci en tout cas.

Alleluia !
Finalement tu avais raison, il y avait bien un problème dans ma requête..

J'avais group by videos.nbre_vues au lieu de nbre_vues (le premier est une colonne, le second l'alias du SUM), je n'avais pas vu passer cette différence..