query

Django Query using .order_by() and .latest()

I have a model: class MyModel(models.Model): creation_date = models.DateTimeField(auto_now_add = True, editable=False) class Meta: get_latest_by = 'creation_date' I had a query in my view that did the following: instances = MyModel.objects.all().order_by('creation_date') And then later I wanted instances.latest(), but ...

Slow Postgres JOIN Query

I'm trying to optimize a slow query that was generated by the Django ORM. It is a many-to-many query. It takes over 1 min to run. The tables have a good amount of data, but they aren't huge (400k rows in sp_article and 300k rows in sp_article_categories) #categories.article_set.filter(post_count__lte=50) EXPLAIN ANALYZE SELECT * ...

How to alter the default value set to a column in a table in SQL?

How can you alter the default value set to a column in a table in SQL. I got an error from: ALTER TABLE tablename.tab ALTER COLUMN mess1 DEFAULT ('hi') What was the correct query? ...

Wordpress query_posts adding two "post-entry" divs, one with "post-thumbnail" and one without.

Hi my name is Antony and this is my first question. I'm currently making a worpress theme. And in the loop I want to have two "post-entry" divs one that's small that goes next to my post thumb, and one for without the thumb. I know that I need to do a <div class="post"> <?php if ( has_post_thumbnail()) { ?><div class="post-thumb"> <...

Selection of records through a join table in Ruby on Rails

I have three models class Collection < ActiveRecord::Base has_many :presentations has_many :galleries, :through => :presentations end class Gallery < ActiveRecord::Base has_many :presentations has_many :collections, :through => :presentations end class Presentation < ActiveRecord::Base belongs_to :collection belongs_to...

Hibernate simple criteria query solving problem

I'm stuck with a very simple criteria query problem: sess .createCriteria(user.class, "user") .user_c.add(Restrictions.eq("user.status", 1)) .user_c.createAlias("user.userCategories","ucs") .add(Restrictions.eq("ucs.category_id",1)); .add(Restrictions.eq("ucs.category_id",'yes')); .add(Restrict...

Query the row which with the maximum value on "cont" column

Hey, I'm trying to query the words which have the maximul values in the cont columns. Ex: if the word "test1" has the value 5 in cont, and "test2" has 2, "test1" will be shown in first position. got it? so. Im trying to do it but it returns the following error: 09-18 22:24:04.072: ERROR/AndroidRuntime(435): Caused by: android.database....

Simplifying this query?

Consider this query: SELECT table1.id, table1.review, table1.time, table2.author, table2.title FROM table1, table2 WHERE table1.id = table2.id AND table1.reviewer = '{$username}' ORDER BY table1.id I'm using the above quite a lot around my site's code. I find that adding the table prefixes etc. befo...

Simple check to see if at least one object in a set has a property value of TRUE

I have a set of model objects that have a public IsVisible boolean property. All I need to do is find if at least one of the set has that value set to TRUE. In other words, if I have 10,000 objects but the second one is true, I don't need to spin through the other 9,998. I already have my answer. Now I know I could write my own itera...

Buliding up a coldfusion query problem with like

I am trying to build a dynamic sql statement with this line <cfset SQL = "SELECT url, MONTH(event_date) AS months, YEAR(event_date) AS year, event_date, title from events where title LIKE '%#form.event_name#%' "> <cfquery name="results" > #SQL# </cfquery> Seems there is a problem with the like clause. Any ideas? Do I need to escape t...

Email Alerts on saved searches, procedure and safety/performance tips&tricks?

I built an email alert for my users (now are only 2,000) so every night a crontab execute a php script that query the mysql to find matches with user's saved search. it's a classified website in my case, but i would like to learn in case i had to build something for bigger clients my concerns are: what happen if my user grow x10 or x...

Getting this query to work?

Heya!, I have the below query: SELECT t1.pm_id FROM fb_user_pms AS t1, fb_user_pm_replies AS t2 WHERE (t1.pm_id = '{$pm_id}' AND t1.profile_author = '{$username}' OR t1.pm_author = '{$username}' AND t1.pm_id = t2.pm_id AND t2.pm_author = '{$username}' AND COUNT(t2.reply_id) > 0) AND t1.deleted = 0 ...

Calculate total score in SQL query from multiple tables

I have the following tables for a competition: User: Id Name Email EntryId Entry: Id Age Gender State GameResult: Id EntryId Duration Score QuestionResult: Id EntryId Correct UsersAnswer Each entry will have multiple games, and multiple questions. I need to perform a query that will find a list of the highest scores, and...

Display SQL query results

I am having trouble displaying results from a SQL query. I am trying to display all images and prices from a products table. I am able to display the echo statement "Query works" in the browser. But, the results are not displaying in the browser. if ($count > 0) { echo "Query works"; } else { ec...

Paramertize Fetch First n Rows Only in DB2

I'm trying to do the following: select * from table fetch first @param rows only @param is an int. DB2 would not have it. I've heard of concatenating it with ||, but I can't seem to get that to work. Anyone have experience with this? (PS I saw a similar question (http://stackoverflow.com/questions/2621420/db2-wont-all...

Problem with Query Data in a Table

I have table test contain 4 fields +----+-----------+--------------+-----+ | id | int_value | string_value | qid | +----+-----------+--------------+-----+ | 1 | 111 | Red | 1 | | 2 | 111 | Green | 2 | | 3 | 111 | Blue | 3 | | 4 | 222 | Yellow | 1 | | 5 | 222 | Red ...

Return only latest values from timestamped table

I have a table with the following structure: timestamp, tagid, value I want a query where I can receive only the newest timestamped values for a list of tag id's. For example: SELECT * FROM floatTable WHERE tagid IN(1,2,3,4,5,6) Will return the entire set of values. I just want the latest stored value for each one. ...

Is it better to query on the FK or the PK in a join query

Using SQL2k5 and assuming the [ID] columns are the clustered PK and the [FK...] columns have a nonclustered index, of the two queries, which WHERE clause is more efficient? SELECT * FROM [table1] INNER JOIN [table2] ON [table2].[ID] = [table1].[FK_table2] INNER JOIN [table3] ON [table3].[ID] = [table1].[FK_table3] WHERE [table1].[FK...

How do you create an index across different tables

Table A consists of (id, fieldA, fieldB, fieldC, fieldD) Table B consists of (id, tableA-id, fieldE, fieldF, fieldG) My queries looks like this:- 1. select * from tableB b, tableA a where a.fieldA=? and a.fieldB=? and a.fieldC=? and a.fieldD=? and (b.fieldF >= '09/01/10' and b.fieldF <= '09/30/10'); 2. select * from tableB b, ...

HQL Query to check if size of collection is 0 or empty

I try to generate a HQL query that include user with a empty appoinment collections(mapped by OneToMany): SELECT u FROM User u JOIN u.appointments uas WHERE u.status = 1 AND (uas.time.end < :date OR size(uas) = 0) I tries it on several manners (NOT EXIST ELEMENT(), IS NULL) also see: http://stackoverflow.com/questions/1105011/how-to-c...