i didn't know title i'm asking myself considered "good" following scenario
table place: id | name | address | etc
table tag: id | tag_name | score
table place_tag id | place_id | tag_id | numberx
for example, application allow user search place based on tag. result should contain name of place top 3 tag place (ordered numberx)
my question best way achieve in java using sql ?
1) select p.name, t.tag_name place p left join ... group p.name,t.tag_name order p.id,pt.numberx desc and in java loop though result (note order p.id) create logic top 3 tag , when detected n+1 id different means i'm processing next place..
2) places = select distinct p.name from..... order p.id tags = select ... place_tag pt pt.place_id in (places.ids) i make 2 separate sql call 1 places 1 tags , find kind of logic link both in java
3) (this easier think worst) places = select p.name place... and loop through places , make call database tag (meaning if loop trough 100 place make 100 call everytime..)
4) ? other pattern ? could more experience alive me on this?
thank you!
doing as possible on sql server side mean less data having travel on network database server application server.
this should close enough scenario, though i'm bringing top 3 tags each place. top 3 based on tag search add where.
here's fiddle it: http://sqlfiddle.com/#!6/9eecb7db59d16c80417c72d1/54
firstly create tables , add data example:
declare @place table ( id int , name varchar(10) , address varchar(20) ) declare @tag table ( id int , tag_name varchar(10) , score int ) declare @place_tag table ( id int identity (1,1) , place_id int , tag_id int , numberx int ) insert @place values (1,'place1','add1') insert @place values (2,'place2','add2') insert @place values (3,'place3','add3') insert @place values (4,'place4','add4') insert @tag values (1,'tag1',3) insert @tag values (2,'tag2',2) insert @tag values (3,'tag3',5) insert @tag values (4,'tag4',4) insert @tag values (5,'tag5',1) insert @tag values (6,'tag6',5) insert @tag values (7,'tag7',3) insert @tag values (8,'tag8',2) insert @tag values (9,'tag9',3) insert @tag values (10,'tag10',4) insert @tag values (11,'tag11',1) insert @tag values (12,'tag12',4) insert @place_tag values (1,1,14) insert @place_tag values (1,2,19) insert @place_tag values (1,3,7) insert @place_tag values (1,4,12) insert @place_tag values (2,5,1) insert @place_tag values (2,6,15) insert @place_tag values (3,7,7) insert @place_tag values (3,8,6) insert @place_tag values (3,9,2) insert @place_tag values (3,10,9) insert @place_tag values (3,11,11) insert @place_tag values (3,12,3) and query. there subquery partitions place and, orders numberx descending, allow outer query top n tags, based on numberx, each place:
select sub.name, sub.tag_name ( select p.name , t.tag_name , row_number() on (partition p.id order pt.numberx desc) rownum @place p left join @place_tag pt on p.id = pt.place_id left join @tag t on pt.tag_id = t.id ) sub sub.rownum <= 3 
Comments
Post a Comment