I need to arrange the list of projects in my site based on the number of bids

Question

I need to arrange the list of projects in my site based on the number of bids (bids are stored in another table). Once a bid is generated, new row of value will be added in the bid table I have to get the bid count for each project and arrange the project based on the count.

In the below code I have used count(bid.project_id) to get the value of bid count of each project but when I use this code I am not able to get the list of all project which has 0 bids.

SELECT DISTINCT p.*,(TO_DAYS(p.start_date) - TO_DAYS(NOW())) AS daydiff,date(DATE_ADD(p.start_date,INTERVAL p.expires DAY)) as end_date,count(bid.project_id) as bidcount FROM c6wz3_jblance_project p
    LEFT JOIN c6wz3_jblance_user ju ON p.publisher_userid = ju.user_id
    LEFT JOIN c6wz3_jblance_custom_field_value cv ON cv.projectid=p.id
    LEFT JOIN c6wz3_jblance_bid as bid ON bid.project_id=p.id
    WHERE (p.project_title LIKE '%%' OR ju.biz_name LIKE '%%' OR cv.value LIKE '%%' OR cv.valuetext LIKE '%%' OR p.description LIKE '%%') AND p.approved=1 AND '2013-04-26 12:18:03' > p.start_date
    GROUP BY bid.project_id
    ORDER BY bidcount ASC

This is the user table:

CREATE TABLE IF NOT EXISTS `c6wz3_jblance_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `biz_name` varchar(100) NOT NULL,
  `status` text NOT NULL,
  `status_access` int(11) NOT NULL DEFAULT '0',
  `posted_on` datetime NOT NULL,
  `picture` varchar(50) NOT NULL,
  `thumb` text NOT NULL,
  `invite` int(11) NOT NULL DEFAULT '0',
  `params` text NOT NULL,
  `latitude` float NOT NULL DEFAULT '255',
  `longitude` float NOT NULL DEFAULT '255',
  `ug_id` int(11) NOT NULL DEFAULT '0',
  `search_email` tinyint(1) NOT NULL DEFAULT '1',
  `notify` tinyint(1) NOT NULL DEFAULT '1',
  `suspend` tinyint(1) NOT NULL DEFAULT '0',
  `suspend_reason` varchar(100) NOT NULL,
  `featured` tinyint(1) DEFAULT '0',
  `rate` float DEFAULT NULL,
  `id_category` varchar(50) NOT NULL,
  `featured_expire` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;

This is the query for bids table:

CREATE TABLE IF NOT EXISTS `c6wz3_jblance_bid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT '0',
  `project_id` int(11) NOT NULL DEFAULT '0',
  `amount` float NOT NULL,
  `delivery` int(11) NOT NULL DEFAULT '0',
  `bid_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `details` mediumtext NOT NULL,
  `outbid` tinyint(1) NOT NULL DEFAULT '0',
  `status` varchar(20) NOT NULL,
  `milestone_perc` int(11) NOT NULL DEFAULT '0',
  `attachment` varchar(255) DEFAULT NULL,
  `is_nda_signed` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `ibid` (`project_id`,`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

This query is used for project table:

CREATE TABLE IF NOT EXISTS `c6wz3_jblance_project` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_title` varchar(255) NOT NULL,
  `id_category` varchar(50) NOT NULL,
  `start_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `create_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `expires` int(11) NOT NULL DEFAULT '0',
  `assigned_userid` int(11) NOT NULL DEFAULT '0',
  `publisher_userid` int(11) NOT NULL DEFAULT '0',
  `status` varchar(32) NOT NULL,
  `budgetmin` float NOT NULL DEFAULT '0',
  `budgetmax` float NOT NULL DEFAULT '0',
  `description` text NOT NULL,
  `is_featured` tinyint(1) DEFAULT '0',
  `profit` float NOT NULL DEFAULT '0',
  `paid_amt` float NOT NULL DEFAULT '0',
  `paid_status` varchar(32) NOT NULL,
  `approved` int(1) NOT NULL DEFAULT '1',
  `profit_additional` float NOT NULL DEFAULT '0',
  `is_urgent` tinyint(1) NOT NULL DEFAULT '0',
  `is_private` tinyint(1) NOT NULL DEFAULT '0',
  `is_sealed` tinyint(1) NOT NULL DEFAULT '0',
  `is_nda` tinyint(1) NOT NULL DEFAULT '0',
  `metakey` text,
  `metadesc` text,
  `buyer_commission` float NOT NULL DEFAULT '0',
  `lancer_commission` float NOT NULL DEFAULT '0',
  `accept_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ;

Answer 1

Change GROUP BY bid.project_id to GROUP BY p.id.

(Projects with no bids will have no bid records - the left outer join ensures that they are included in the dataset, but grouping on bid.project_id ensures that they will all be lumped together in a single row with a NULL project ID.)