Sharath Upadhya

Greenhorn
+ Follow
since Dec 01, 2009
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by Sharath Upadhya

Thanks a lot `Jeanne Boyarsky` and `Scott Selikoff` for the suggestion.

Suppose, I have made it into 2 tables, one for topics and another for comments, then how would you order them by posted date.

If your answer is 'fetch both comments and topics separately, and then order them both by putting in the list', then wouldn't that be inefficient ?

or is there any way in mysql to merge both columns for the ordering purpose ?

help me people......

thanks
If you want to fetch only the last row, you can use 'limit', like this.with this query, you can fetch the whole record. if you want to fetch only the last ID, you can try using aggregate function. like thisassuming the column is an integer
Let me remind you that i am using a single table `discussions` to store both topics as well as their comments.

This `discussions` table contains about 190 records. out of which, 19 records are topics(they do not have parent_discussion). remaining 171 records are comments. we are leaving out all the old comments and fetching only the latest comment per discussion.(the group by clause). and topics themselves if they do not have comments.

so it makes sense to fetch only 19 records.
Hi Jeanne Boyarsky, thanks for the reply.

by the way, the query I posted above fulfills my requirement. I assume you had trouble understanding my requirement.

let me break down the query.

This line returns all the records from the discussions table. where.. the condition goes...

whose id includes those records, which have parent discussion(i.e. these are comments). group by desc ensures that only the latest comment per discussion is fetched. and this sub-query fetches only the comments.

the 'OR' specifies that, record can be either a topic or a discussion.

This sub-query fetches all the records, that are topics. (i.e they do not have parent). and also ensures that they do not include any of those topics that are having comments and were fetched in the previous sub-query.

This line sorts all the records by their posting/commenting date with the latest on top.

If you are still not sure about this, take a look at the list of records after I ran this query.


We have 19 topics here, and only 15 topics have comments.

I hope this clarifies your doubts.
I am having a table called discussions. it contains all the topics as well as comments.

I want to fetch all the topics and their latest comments at once. if there is no comment for a topic, then topic is fetched or else the latest comment.

for example, if there are 19 topics, and 15 topics have comments, then the latest comments of those 15 topics and the remaining 4 un-commented topics are fetched.

I am using multiple sub-queries to fetch them at once. like the one below.

select * from discussions where id in (select id from discussions where parent_discussion is not null group by parent_discussion desc) or id in (select id from discussions where parent_discuss
ion is null and id not in (select distinct parent_discussion from discussions where parent_discussion is not null)) order by created_on desc;

here the first sub-query fetches all the latest comments and the second one fetches all the topics that have no comments.

here's my question to you guys. "How much efficient is this query ? do you have any other way around." Your suggestion would be of a great help. thanks in advance.
I am using multiple sub-queries in both queries.

but I haven't posted those queries just for simplicity. and to give you an idea of what am trying to accomplish.

and it cannot be done without sub-queries as you know it.
thanks for responding. here's my JPQL query.

SELECT x FROM Discussions AS x WHERE x.id IN (SELECT y.id FROM Discussions AS y WHERE y.parentDiscussion IS NOT NULL GROUP BY y.parentDiscussion DESC);

This is just a simplified query. if I remove 'DESC', i am getting the output. but not the expected one.

without DESC, this query fetches only the first comment for any discussion. my requirement is to fetch only the latest comments for all the discussions.
I am having issues in using the 'group by' clause in JPQL.

I need to fetch all the records grouped by their parent records, but need to group by the latest record.(grouping in reverse order)

I tried running 'select * from table_name group by parent_record desc' on mysql and there was no problem.

but when I tried it with JPQL, it started giving exceptions.

please guys, help me if anyone of you have tried this....

thanks in advance.
I have created two entities, namely Country, Drug, and I have established a manytomany relationship between them.
So I have the third table 'country_brand_drug_name' generated. my problem is I am not able to access the third table with jpa implementation. when I try to save the records in the third table, it is not giving any exception, but it is simply not saving, and if I insert some records manually into the third table, and try to read them with jpa implementation, it is not reading, and giving null pointer exception.
I am using openJpa for jpa implementation.

please help me with a solution

@Entity
@Table(name = "brand_drug_name")
public class BrandDrugName {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private long id;

@Column(name = "brand_name")
private String brandDrugName;

@ManyToMany(cascade = { CascadeType.MERGE, CascadeType.REMOVE })
@JoinTable(name = "country_brand_drug_name", joinColumns = @JoinColumn(name = "brand_drug_name_id"),
inverseJoinColumns = @JoinColumn(name = "country_id"))
private Set<Country> countrySet;

getters() and setters()..............
}

@Entity
@Table(name = "country")
public class Country extends CompanyEntity{

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private long id;

@Column(name = "name",nullable = false)
private String name;
private String code;
private String description;

@ManyToMany(cascade = { CascadeType.MERGE, CascadeType.REMOVE }, mappedBy = "countrySet")
private Set<BrandDrugName> brandDrugNameSet

getters() and setters()..........

}

public class JPASave {
public void save(long countryId, Set<BrandDrugName> drugSet) {
Country country = em.find(Country.class, countryId);
country.setBrandDrugNameSet(drugSet);
em.getTransaction().begin();
em.merge(country);
em.getTransaction.commit();
}

}