SELECT /* MAIN SEARCH QUERY */
	
	pm.name AS manufacturer,
	pm.name AS manufacturer_name,
	P.id,
	P.id AS product_id,
	P.code,
    P.cost_price,
    P.vat_enabled,
	P.description,
	P.sold_retail,
	P.sold_trade,
	P.sold_wholesale,
    P.country_origin_code,
    P.commodity_code,
    P.price_set,
    P.width,
    P.height,
    P.length,
    P.product_scale_id,
    P.reorder_level,
    P.release_date,
    P.weight,
    P.restriction,
    P.restriction_bit,
    P.active,
    P.minimum_level,
    P.second_hand,
    P.limited_availability,
    P.can_reorder,
    P.warehouse_zone_id,
    P.wholesale_customers,
    P.new_price,
    P.big_postage,
    P.disable_min_order_amount,
    P.last_updated,
    P.notes,
    P.old_product_scale_txt,
    P.has_image_200,
    P.has_image_400,
    P.last_purchase_order_stock,
    P.last_purchase_order_date,
    P.first_delivered_date,
    P.last_moved,
    P.scale_string,
    P.keywords,
    P.finished_date,
    P.availability,
    P.sold,
    P.sold_total,
    P.sold_retail,
    P.sold_trade,
    P.sold_wholesale,
	P.base_price,
	P.base_export_price,
	P.retail_discount,
	P.eu_price,
	P.export_price,
	P.trade_price,
	P.trade_export,
	P.wholesale_price,
	P.wholesale_export,
	P.discount,
	P.backorder_retail_discount,
	P.backorder_discount,
	P.trade_discount,
	P.wholesale_discount,
	P.product_category_id,
    P.product_division_id,
	P.product_type_id,
	P.product_manufacturer_id,
	GROUP_CONCAT(DISTINCT ps.scale ORDER BY ps.scale ASC SEPARATOR ', ') AS scale,
	UNIX_TIMESTAMP(P.date_added) AS date_added,
	UNIX_TIMESTAMP(P.arrival_date) AS arrival_date ,
	DATE(P.arrival_date) AS real_arrival_date ,
	pt.name AS product_type,
	pt.name AS type_name,
	media.filename,
	IF (pp.id is null,0,1) as pending,
	
	if(
		coalesce( (coalesce(sl.count , 0) - coalesce(sc.product_quantity , 0 ) ) , 0 ) >= 0  ,
		if(
			coalesce( (coalesce(sl.count , 0) - coalesce(sc.product_quantity , 0 ) - coalesce(bv.backorders, 0 ) - coalesce(bv.free_backorders, 0 ) ) , 0 ) >= P.show_stock,
			P.show_stock ,
			coalesce( (coalesce(sl.count , 0) - coalesce(sc.product_quantity , 0 ) - coalesce(bv.backorders, 0 ) - coalesce(bv.free_backorders, 0 ) ) , 0 )
		)
	    , 0
	) as available,
    COALESCE(sl.count,0) as stock_level,
    COALESCE(sc.product_quantity,0) AS stock_committed,
    COALESCE(bv.backorders,0) AS backorders,
    COALESCE(bv.free_backorders,0) AS free_backorders,
	pc.id as department_id,
	pc.name as department,
	pc.name as department_name,
	P.show_stock,
	if(
		pc.name = 'Future Releases',
		true,
		false
	) AS future,
    unix_timestamp(media.date_uploaded) as unixtime



FROM product P
LEFT JOIN stock_level sl on P.id = sl.product_id
LEFT JOIN stock_committed sc on sc.product_id = P.id
LEFT JOIN backorder_values bv ON bv.product_id = P.id
LEFT JOIN product_category pc on P.product_category_id = pc.id
LEFT JOIN product_manufacturer pm ON P.product_manufacturer_id = pm.id
LEFT JOIN product_media media ON P.id = media.product_id AND `media`.`order` = 0

JOIN
product_scale_map AS psm ON (psm.product_id = P.id)

LEFT JOIN
product_scale ps ON (psm.scale_id = ps.id)

LEFT JOIN product_type pt ON P.product_type_id = pt.id
LEFT JOIN pending_product pp ON P.id = pp.id
WHERE 1  AND P.restriction_bit & 1 = 1 AND P.price_set = 1 
		 AND P.product_category_id = 105 
		AND pc.show_in_search = 1

	 AND (P.product_category_id = 103 OR P.price_set) GROUP BY P.id ORDER BY DATE_FORMAT(P.arrival_date,"%Y-%m-%d %H")   DESC  ,  P.code 
New Arrivals | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

New Arrivals



Found 1,954 results
| View per page
Refine your search

Deprecated: Creation of dynamic property Hannants\SearchFilters::$searchDirection is deprecated in /mnt/vault/Sync/Dessol/Sites/hannants.co.uk/php_includes/classes/SearchFilters.php on line 115

Scale

Product Type

Manufacturer

Added Within

Manufacturer & Catalogue No ASC DESC
Scale ASC DESC
Description ASC DESC
Type ASC DESC
Department ASC DESC
Delivered ASC DESC
Price ASC DESC
Watch
Stock ASC DESC
Buy

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Sepecat Jaguar tow bars wheels up & down - 3D-printed
Diorama accessories (resin)
New Arrivals
16/04/2024
£19.80
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
1:48
Huck starteré - RFC circa 1918 - 3D-printed
Diorama accessories (resin)
New Arrivals
16/04/2024
£38.00
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Clayton Crawler tractor - RFC circa 1917 - 3d-printed
Diorama accessories (resin)
New Arrivals
16/04/2024
£30.60
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Leyland Subsidy A type fuel lorry - circa 1916 - 3D-Printed
Diorama accessories (resin)
New Arrivals
16/04/2024
£27.00
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

RE-RELEASED!!! RAF Sentinel Tugmaster tractor (V-bomber support series). 3D-Printed resin, new highly detailed design - includes interior detail for electrical generator and cable reels, with separate cover panels. (designed to be used with Airfix kits) [of the Avro Vulcan - all versions, Handley-Page Victor - all versions, and Vickers Valiant - all versions] RAF Sentinel Tugmaster tractor (V-bomber support series). 3D-Prin...
Diorama accessories
New Arrivals
16/04/2024
£27.00
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

1:72
RE-RELEASED!!! BAC TSR-2 Tow Bar (designed to be used with Airfix kits). For more information on this product, please click the link to go to the Aircraft In Miniature web page.
Diorama accessories
New Arrivals
16/04/2024
£10.80
-
In Stock
1

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
1:72
Huck starteré - RFC circa 1918 - 3d-printed
Diorama accessories (resin)
New Arrivals
16/04/2024
£21.60
£19.44
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
1:72
Clayton Crawler tractor - RFC circa 1917 - 3d-printed
Diorama accessories (resin)
New Arrivals
16/04/2024
£18.00
£16.20
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Leyland Subsidy A type fuel lorry - circa 1916 - 3d-printed
Diorama accessories (resin)
New Arrivals
16/04/2024
£18.00
£16.20
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Leyland Subsidy A type flatbed lorry - circa 1916 - 3d-printed
Diorama accessories (resin)
New Arrivals
16/04/2024
£16.20
£14.58
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Anlasswagen - Luftwaffe External Power Supply Unit - WWII - 3d-printed. (designed to be used with the Airfix kits) of the Junkers Ju-87B-2 'Stuka' , and the Trumpeter kits of the Messerschmitt Bf-109G, Focke-Wulf Fw-190D-9, Junkers Ju-87A 'Stuka', Junkers Ju-87B-2 'Stuka', Junkers Ju-87B-2/U4 Stuka, Junkers Ju-87D-5 'Stuka' , Junkers Ju-87R 'Stuka' or Junkers Ju-87G-2 'Stuka' Anlasswagen - Luftwaffe External Power Supply Un...
Diorama accessories (resin)
New Arrivals
16/04/2024
£32.40
£29.16
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Olwagen - Luftwaffe Oil Service Cart - WWII 3d-printed. (designed to be used with the Airfix kits) of the Junkers Ju-87B-2 'Stuka' , and the Trumpeter kits of the Messerschmitt Bf-109G, Focke-Wulf Fw-190D-9, Junkers Ju-87A 'Stuka', Junkers Ju-87B-2 'Stuka', Junkers Ju-87B-2/U4 Stuka, Junkers Ju-87D-5 'Stuka' , Junkers Ju-87R 'Stuka' or Junkers Ju-87G-2 'Stuka' Olwagen - Luftwaffe Oil Service Cart - WWII 3d-printed....
Diorama accessories (resin)
New Arrivals
16/04/2024
£27.00
£24.30
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Ladewagen LWC 500IX - Luftwaffe bomb loader - WWII 3d-printed. (designed to be used with the Airfix kits) of the Junkers Ju-87B-2 'Stuka' , and the Trumpeter kits of the Junkers Ju-87A 'Stuka', Junkers Ju-87B-2 'Stuka', Junkers Ju-87B-2/U4 Stuka, Junkers Ju-87D-5 'Stuka' , Junkers Ju-87R 'Stuka' or Junkers Ju-87G-2 'Stuka' (designed to be used with the Airfix kits)
Diorama accessories (resin)
New Arrivals
16/04/2024
£14.40
£12.96
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Kettenkrad tractor (Sd Kfz. 2) - Luftwaffe Half-track Tractor - WWII 3d-printed. (designed to be used with the Airfix kits) of the Junkers Ju-87B-2 'Stuka' , and the Trumpeter kits of the Messerschmitt Bf-109G, Focke-Wulf Fw-190D-9, Junkers Ju-87A 'Stuka', Junkers Ju-87B-2 'Stuka', Junkers Ju-87B-2/U4 Stuka, Junkers Ju-87D-5 'Stuka' , Junkers Ju-87R 'Stuka' or Junkers Ju-87G-2 'Stuka' Kettenkrad tractor (Sd Kfz. 2) - Luftwa...
Diorama accessories (resin)
New Arrivals
16/04/2024
£18.00
£16.20
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
1:32
GBU-28B Paveway III Bunker Buster (pack of 2) 3d-printed
Aircraft detailing sets (resin)
New Arrivals
16/04/2024
£27.00
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Paveway IV UK Specification (pack of 2) 3d-printed
Aircraft detailing sets (resin)
New Arrivals
16/04/2024
£21.60
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
GBU-28B Paveway III Bunker Buster (pack of 2) 3d-printed
Aircraft detailing sets (resin)
New Arrivals
16/04/2024
£18.00
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Paveway IV UK Specification (pack of 2) - 3d printed
Aircraft detailing sets (resin)
New Arrivals
16/04/2024
£10.80
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
RE-RELEASED! AN/ASQ-236 Dragon's Eye Radar pod " two pod pack of 3D-Printed parts. (designed to be used with Academy, Great Wall Hobby and Italeri kits of the McDonnell-Douglas F-15E Strike Eagle, Hasegawa, Hobby Boss, Italeri, Frog, PM Models and Tamiya kits of the Lockheed-Martin F-16C, and Hasegawa, Hobby Boss and Revell kits of the F-16D).
Aircraft detailing sets (resin)
New Arrivals
16/04/2024
£14.40
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
RE-RELEASED!!! USAF/USN & NATO B61 early/cold war nuclear weapon twin pack. Two weapons with maintenance/transportation dollies - these are highly detailed 3D-Printed resin parts.
Aircraft bombs and rockets (resin)
New Arrivals
16/04/2024
£14.40
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
GBU-28B Paveway III Bunker Buster (pack of 2) 3D-printed
Aircraft detailing sets (resin)
New Arrivals
16/04/2024
£9.00
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Paveway IV UK Specification (pack of 2) 3D-printed
Aircraft detailing sets (resin)
New Arrivals
16/04/2024
£7.20
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
U.S.Trailer with generator
Military vehicle kits (resin)
New Arrivals
16/04/2024
£49.99
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Kneeling pilot for the North-American F-86F-40 Sabre (designed top be used with Hasegawa kits) but will be suitable for many other 1950's USAF aircraft
Figures (resin)
New Arrivals
16/04/2024
£17.99
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mitsubishi A6M Zero pilot standing
Figures (resin)
New Arrivals
16/04/2024
£17.99
Out of Stock
0