How to fetch child locations and sub child locations by parent location?
I want to fetch child and sub child locations by parent
location.
For example, I have location structure as follows:
-HTL
-India
-Karnataka
-Banglore
Jaynagar
-Maharashtra
Puna
Example 1
I want to fetch child locations of India – means it should fetch sub children of its children.
It should fetch Karnataka, Maharashtra with their children.
Example 2
If I want to fetch children of Karnataka location means it should fetch banglore and also jaynagar.
In my database table, I have one field has_children which
will be set to 1 if it has child location. If location does not have any child
location then the has_children will be set to 0.
I have written the following query to fetch the child
locations:
@Override
public List<Location> getChildLocations(Long pk_location_id) {
Query query = em.createQuery("SELECT loc FROM Location loc where loc.deleted=0 and " + "loc.parent_location_id="+pk_location_id);
return (List<Location>) query.getResultList();
}
But for this query I am getting only the main children of
the location. For example, if I passed the pk_location_id
of India, then I am getting only the children of India which are Karnataka and Maharashtra.
But I want to fetch the sub children of these locations also.
If I passed one location id means it should check the
has_children field of the children location also if that field is set to 1
means again it should fetch the children of those sub location also and so on.
My DB table structure is as follows:
CREATE TABLE `location` (
`pk_location_id` bigint(20) NOT NULL,
`created_date` datetime DEFAULT NULL,
`deleted` int(11) DEFAULT NULL,
`has_children` bit(1) DEFAULT NULL,
`location_city` varchar(50) DEFAULT NULL,
`location_country` varchar(50) DEFAULT NULL,
`location_description` varchar(255) DEFAULT NULL,
`location_fax` varchar(50) DEFAULT NULL,
`location_phone` varchar(20) DEFAULT NULL,
`location_postal_code` varchar(20) DEFAULT NULL,
`location_roles_id` bigint(20) DEFAULT NULL,
`location_state` varchar(50) DEFAULT NULL,
`location_street_address` varchar(50) DEFAULT NULL,
`locationcode` varchar(20) DEFAULT NULL,
`locationname` varchar(50) NOT NULL,
`locationpath` varchar(100) DEFAULT NULL,
`modified_date` datetime DEFAULT NULL,
`parent_location_id` bigint(20) DEFAULT NULL,
`tenant_id` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `location`
--
INSERT INTO `location` (`pk_location_id`, `created_date`, `deleted`, `has_children`, `location_city`, `location_country`, `location_description`, `location_fax`, `location_phone`, `location_postal_code`, `location_roles_id`, `location_state`, `location_street_address`, `locationcode`, `locationname`, `locationpath`, `modified_date`, `parent_location_id`, `tenant_id`) VALUES
(1, NULL, 0, b'1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '001', 'HTL', NULL, NULL, 0, NULL),
(2, NULL, 0, b'1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'India', NULL, NULL, 1, NULL),
(3, NULL, 0, b'1', NULL, NULL, NULL, '8765', NULL, NULL, NULL, NULL, NULL, '4444', 'Karnataka', NULL, NULL, 2, NULL),
(4, NULL, 0, b'1', NULL, NULL, NULL, '876', '9876543211', NULL, NULL, NULL, NULL, 'MA112', 'Maharashtra', NULL, NULL, 2, NULL),
(5, NULL, 0, b'1', NULL, NULL, NULL, '3333', '444444444', NULL, NULL, NULL, NULL, 'ba2222', 'banglore', NULL, NULL, 3, NULL),
(6, NULL, 0, b'0', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'ja111', 'jay nagar', '/banglore/Karnataka/India/HTL', NULL, 5, NULL),
(7, NULL, 0, b'0', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'pu111', 'puna', '', NULL, 4, NULL);
How to implement this using spring boot?
Thanks & Regards,
Shilpa Kulkarni
