[Clarence]

Ursprungligen postat av danjel

Intressant, har tidigare funderat på varför SP's anses mer av "best practice" i asp.net världen medans det i php/mysql inte är så diskuterat eller använt.

Hur brukar ni hantera stora SQL frågor, lägger ni dem i ett "datalager" ?
Det känns bökigt när man har sql frågor med typ 10 joins att lägga in dem i en vanlig php sträng, jag har tidigare övervägt att lägga in dessa i SP's istället för att få enklare underhåll.

Vad man generellt föredrar är mycket av en smakfråga, det finns argument åt båda hållen. Jag tror att det mest avgörande är huruvida man oftare jobbat i organisationer med dedikerade DBAs/databasutvecklare eller en organisation där specifikationerna av affärslogiken är väldigt fasta och strukturerade. Då tror jag man oftare föredrar SPs.

Själv föredrar jag generellt att hålla mig borta från SPs av främst 2 anledningar.
- Lättare byggprocess och inget extra att tänka på vid förändringar i affärslogik utan förändringar i databasstruktur (vilket iallafall under mina 15 år med webb varit mycket mycket vanligare).
- Snabbare utvecklings i ett välutvecklat scriptspråk vilket leder till fler personer med kompetens.

Och alternativen är inte SPs eller en sträng i ett scriptspråk. Query builders, content repositories, ORMs osv finns bibliotek för i alla språk.

Vidare vet jag inte huruvida det är så etablerat som best practice generellt i .NET. Använder du entity framework t ex är det väl snarare tvärtom (går att använda men är manuellt extrapill, tror jag?). Och det är väl ändå välanvänt?

[danjel]

Ursprungligen postat av Clarence

Och alternativen är inte SPs eller en sträng i ett scriptspråk. Query builders, content repositories, ORMs osv finns bibliotek för i alla språk.

Ja, lite off topic men mina erfarenheter av ORM's , Query builders , är inte så omfattande i php men är direkt hemska när det gäller avancerade frågor. Om man nu inte har stenkoll på det verktyg som används.
Men givet att man vill använda ren sql är det väl endast Sp eller sträng sql som gäller,eller Vyer.

Ursprungligen postat av Clarence

Vidare vet jag inte huruvida det är så etablerat som best practice generellt i .NET. Använder du entity framework t ex är det väl snarare tvärtom (går att använda men är manuellt extrapill, tror jag?). Och det är väl ändå välanvänt?

Ja det är sant , SP's försvinner en del vad gäller EF (eller motsvarande ORM's) som ju är tämligen poulärt nu.
Men har man SQL direkt i c# koden brukar en del tycka det är rätt illa. Säker delvis p.g.a att det krävs omkompilering av Visual Studio projektet för att ändra sql:en.

[secag]

Har ett nytt problem nu.

Koden för att få "total_power" ifrån enskilda spelare fungerar bra. Men nu har jag en tabell med en lista på spelare där jag måste räkna ihop allas "total_power" till en enda stor summa. Vi kan kalla den "MEGA_TOTAL_POWER".

Jag tror att man någonstans skall lägga en s.k. sub-query?

Här är mitt försök vilket ger mig ett felmeddelanden som jag inte kan förstå:

PHP-kod:

SELECT *,
       SUM
  (SELECT CASE WHEN items.item_stat IS NULL THEN members.member_attack + members.member_defence ELSE SUM(items.item_stat) + members.member_attack + members.member_defence END AS total_power
   FROM members
   LEFT JOIN memberitems ON members.member_id = memberitems.memberitems_member
   LEFT JOIN items ON memberitems.memberitems_item = items.item_id
   GROUP BY members.member_id) AS MEGA_TOTAL_POWER
FROM crews
WHERE id = 1 


Här är tabellen 'crews'. Denna lagras alla olika allianser/grupper/klaner.

PHP-kod:

CREATE TABLE IF NOT EXISTS `crews` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `money` int(11) NOT NULL,
  `tax` tinyint(3) unsigned NOT NULL,
  `attack` int(10) unsigned NOT NULL,
  `defence` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `crews` (`id`, `name`, `money`, `tax`, `attack`, `defence`) VALUES
(1, 'MuggemGansters', 0, 0, 0, 0); 


Här är tabellen som lagras alla som är med i laget.

PHP-kod:

CREATE TABLE IF NOT EXISTS `crew_members` (
  `crew_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` tinyint(4) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `crew_members` (`crew_id`, `user_id`, `status`) VALUES
(1, 1, 9); 


Hittade denna länk om det kan vara till min hjälp?
http://www.sqlteam.com/article/aggre...ed-sub-queries

[danjel]

Jag skulle nog försöka dela upp det i fler queries och undvika sub queries.

Den länken gäller sql server så det kan vara felaktig info även om sql standarden kan vara korrekt.

[secag]

Flera querys har väl en tendens att bli jobbiga? Alltså mer krävande?

[danjel]

Ursprungligen postat av secag

Flera querys har väl en tendens att bli jobbiga? Alltså mer krävande?

Det beror på. Det man ska undvika är att köra querys i php loopar.
Men det kan faktiskt gå snabbare om du istället för en avancerad sql kör två, tre enskilda queries och kanske gör vissa summeringar i php istället, vilket avlastar databasen.
Det kan även vara lättare att felsöka och optimera de enskilda frågorna mha EXPLAIN Select.
Nu har jag svårt att greppa just detta case, men om det går att dela upp det så tycker jag du ska testa det..

[secag]

Okej men jag kan förklara hur det ska vara.

En tabell heter crews, den hämtar jag all information ifrån "FROM crews"

En tabell som heter crew_members där det kan finnas upp till 80 rader. Här gör jag "JOIN crew_members ON crews.id = crew_members.crew_id"

Och crew_members innehåller endast user_id & crew_id. Ifrån detta user id så vill jag hämta en hel del information(åtminstone username och sen total_power). Vill inte loopa en query 80 gånger, känns som det blir mycket segare då.

[Conny Westh]

Kan du bidra med koden för att få fram total_power som väl fungerar.

Kan du bidra med fler INSERT-satser så man kan få lite testdata att jobba med?

[lubic]

Har inte helt koll på vad du vill göra och eller hur dina tabeller ser ut, men om jag inte är helt fel ute så borde du kunna göra något liknande detta:

SELECT m.username (eller liknande för att plocka ut de kolumner du vill ha)
FROM crew c
JOIN crew_members cm on cm.crew_id = c.crew_id
JOIN members m on m.user_id = cm.user_id
WHERE c.crew_id = 1 (eller vilket id du vill hämta ut medlemmarna ifrån)

Vill du även ta ut t.ex. total_power så får du joina på de tabeller som innehåller informationen om detta (items? om jag minns rätt från tidigare poster?) samt gruppera på m.user_id och göra en summering på alla items för varje medlem.

[secag]

Löste det på egen hand..

PHP-kod:

SELECT SUM(t.totPower) AS total_crew_power
FROM crew_members cm
JOIN members m ON m.member_id = cm.user_id
JOIN
  (SELECT m.member_id,
          (SUM(i.item_stat) + MAX(m.member_attack) + MAX(m.member_defence)) AS totPower
   FROM members m
   JOIN memberitems mi ON m.member_id = mi.memberitems_member
   JOIN items i ON i.item_id = mi.memberitems_item
   GROUP BY m.member_id)t ON t.member_id = m.member_id
WHERE cm.crew_id = {$id}
GROUP BY cm.crew_id 


Nytt problem!

Med denna query:

PHP-kod:

SELECT members.*, 
CASE WHEN items.item_stat IS NULL 
THEN 
members.member_attack + members.member_defence
ELSE 
SUM(items.item_stat) + members.member_attack + members.member_defence
END AS total_power
FROM members
LEFT JOIN memberitems ON members.member_id = memberitems.memberitems_member
LEFT JOIN items ON memberitems.memberitems_item = items.item_id
GROUP BY members.member_id
ORDER BY total_power DESC, members.member_id LIMIT 20 


så vill jag också hämta memberitems för varje member som jag gör med denna query:

PHP-kod:

SELECT *
FROM `memberitems`
JOIN `items` ON items.item_id = memberitems.memberitems_item
WHERE memberitems.memberitems_member = {MEMBER_ID} 


Problemet nu är att första queryn ger en row per member men varje member kan ha flera items. Om detta ska gå måste jag då göra en ny tabell i queryn där jag lägger alla memberitems i en column och sedan gör en SELECT på den columnen och sedan tömmer den tabellen mellan varje member och tar bort den när queryn är klar.

Hoppas att någon hänger med i min tankegång eller har en bättre lösning