20200611_neuron-reconstruction-list.sql

--95 human, 527 mouse= 622 reconstructions for June 2020
SELECT p.code AS csp_project, d.organism_id, csp.id AS csp_id, csp.name AS csp, csp.patched_cell_container, csp.ephys_qc_result AS csp_eqr, err.id AS err_id, err.workflow_state AS err_state, 
nrwkf.storage_directory || nrwkf.filename AS nrwkf, a.name AS age, d.external_donor_name
,st.acronym AS csp_structure, cr.name AS cell_reporter_name, csl.id AS csl_id, csl.normalized_depth, err.published_at AS err_published, nrwkf.published_at AS nrwkf_published
,sta.name AS apical_specimen_tag, std.name AS dendrite_specimen_tag
FROM specimens csp JOIN specimens sp ON sp.id=csp.parent_id JOIN donors d ON sp.donor_id=d.id JOIN projects p ON p.id=csp.project_id LEFT JOIN ephys_roi_results err ON csp.ephys_roi_result_id=err.id 
JOIN ages a ON a.id=d.age_id 
JOIN structures st ON st.id=csp.structure_id 
JOIN cell_reporters cr ON cr.id=csp.cell_reporter_id
JOIN cell_soma_locations csl ON csl.specimen_id=csp.id
JOIN neuron_reconstructions nr ON nr.specimen_id=csp.id AND nr.manual = 't' AND nr.superseded = 'f'
JOIN well_known_files nrwkf ON nrwkf.attachable_id=nr.id AND nrwkf.well_known_file_type_id = (SELECT id FROM well_known_file_types WHERE name = '3DNeuronReconstruction')
JOIN specimen_tags_specimens stsa ON stsa.specimen_id=csp.id JOIN specimen_tags sta ON sta.id=stsa.specimen_tag_id AND sta.name LIKE 'apical - %' --AND sta.name NOT LIKE 'apical - NA'
JOIN specimen_tags_specimens stsd ON stsd.specimen_id=csp.id JOIN specimen_tags std ON std.id=stsd.specimen_tag_id AND std.name LIKE 'dendrite type - %' AND std.name NOT LIKE 'dendrite type - NA'
WHERE csp.id IN
(541549258,541557114,569835804,569860121,571732727,576110753,576118161,576134298,576140393,592532014,595572609,595583062,596898838,602165685,653040833,653804581,653854241,654180841,677088033,685767564,685806309,685814252,685815936,689300528,689306818,689309060,689312221,689323433,689360253,689370040,695494556,695521538,695546566,695557339,701072592,707828926,720828444,720850977,720862326,720871158,720878801,737089555,737134157,737158657,737293981,737424303,737820466,737988336,758285403,758286194,758311336,758312441,758319694,758323401,758816993,758967755,758996848,767415421,767422070,767433014,767829778,768819569,768848167,768867010,768885440,768904007,769228370,770255008,770255641,770268817,770275864,770307286,770337451,770369798,770402036,770516177,770611301,770645883,770650205,770658546,774420848,774421995,774620186,783585601,783585742,783615441,783624438,783698501,785378321,785379684,793490645,793647709,794276683,797978191,832627767,601506507,601790961,601803754,601808698,601810307,601811091,602231708,602535278,604646725,605060256,605160536,606098116,606271263,606308411,614728680,623326230,623415197,623434306,626153003,627881581,637846989,638212024,639004612,639013022,639674270,639708328,639797308,639809582,639818525,639841063,639862877,639881631,639893239,639962984,640006296,640012241,640061596,640111844,640280740,642124659,643536919,643702874,643727497,643754730,644571405,644920227,644941196,644994103,645012440,645057881,645169930,645322005,645355846,646805498,646852171,647045788,648139266,648582997,649387261,652299236,652300499,652305423,652306478,652516963,652533530,652812484,653327513,653699672,656156755,656705176,656706125,656901554,657184390,657442490,657454163,657661669,657661984,657857340,657858349,657983207,657984709,657985196,657999088,658012080,659012847,659395808,659405820,659405868,659413418,659697295,660050258,661189529,661318431,661319074,661422439,661481525,661492805,662580082,663193112,666141992,667473785,667492234,668176228,668337999,668464797,669047778,669072736,669074640,669088423,669165397,669523148,669825289,669838950,670091720,670340941,673132928,673395815,673427380,674069192,674495385,679457550,679899997,679932490,680027413,680108584,681793778,682332013,682454193,683899155,685624703,685636842,685707621,687907602,688115867,688145929,688958418,689030611,689098357,689280134,689656936,691511791,691665864,691685873,691719142,692015490,692044280,692083084,692286150,692310663,692565557,692777680,692821749,692824134,692846027,693009119,693085460,694146546,694162255,694183636,694220357,694419620,694437359,694689662,694734857,696115308,697502029,697787349,697795277,697799017,697810838,698042345,698086962,698570301,698586385,699448311,700390241,700413526,701075567,701140434,702051128,703283610,703565379,704047023,705217315,705278421,705570328,705817510,707162377,707179690,707403326,707424705,707666779,708221965,709153201,709219898,710338421,710730520,710743304,710784375,710920450,711212623,711464852,711483278,711528753,711571947,711770582,711903629,713132614,713442456,714046701,714228701,714485370,714601733,714755043,714936352,715159651,716298521,716832846,717675736,718135969,718188717,718195284,718202813,718205473,718237581,718257664,718494231,718618263,718636190,718640845,718643723,718644056,718647510,718656998,718872930,720186469,720207494,720678722,722795299,722821387,728756295,729135271,730744363,730796336,731651186,731683221,731733715,731765828,731882356,731917470,736884821,738041476,738401019,738859085,739547189,739558698,739570508,739810705,739840659,740094065,740110275,740134630,740135032,740155221,740164794,740373591,740380531,740401195,740649731,740660643,740675145,740695122,740732467,740780456,740819727,740872967,740875222,741187985,741313892,741785767,741788179,741796557,741940660,742009621,742033571,742041338,742049511,742054277,742784488,743001170,743012953,743041228,743049614,743468218,743504177,743675437,743688686,744053590,744454952,744476851,744975652,745584401,745633800,745692039,745736817,746086313,746200101,746257405,746732672,746747868,746754997,746917764,746924803,747028632,750472605,750706642,750776506,750824726,750833190,751199266,751947558,751991773,752027250,752042986,752063316,754508194,755552030,755592855,755625739,755625849,758263126,759751429,759909292,759939473,759948010,759955697,759970262,760011270,760064682,760268259,760280955,760294832,760316107,760416458,760821319,760832993,760834631,760890074,760961855,760981484,761297232,761304075,761344650,761409397,761429710,761820827,761822060,762590075,762757594,763912380,763952780,764341238,764391439,764726542,764872932,764879591,764896381,764897253,764902677,764902707,766097293,768763533,770216620,770238836,778327002,779708399,786135798,786176775,786228558,788296224,795530674,795563325,795572082,795844214,796636728,796666943,797664123,797722932,799776604,803605276,804968066,804977071,805584825,808285808,808475329,808537562,808824183,808875776,808882292,809006343,809113087,809338969,809649556,809688289,810317285,812996960,816012073,816434177,817935092,818792182,820754704,820897104,821563005,821771315,821774757,821788449,821907565,823253200,823681740,823702943,823719680,823830483,824909637,824937690,825269133,825522397,825549510,826712572,827347629,827447865,827524052,827573535,828803869,829170676,829185764,829195312,831175106,831988252,836844418,837594867,841718359,841834394,842841654,842844583,843366621,844757902,844870606,844965212,846902552,847083836,847876759,849535368,849607183,851016910,853882442,855275163,855357345,857223181,857296784,859621160,859655391,861765455,861769605,863585604,863604233,863631899,866267921,867650384,869634031,870661094,872026690,872248529,873854264,886335606,887048277,887288382,888108541,889183564,889235347,889271408,895740736,898559010,898697809,898703349,898881170,899368360,899583778,904023586,904832267,904862358,910375338,912289883,912301547,912340608,912868696,912889454,912918179,913928092,914412754,915479566,919102928,919699890,919883526,919950532,922613228,924610453,925242241,926816093,926866005,926887643,927833265,929533489,935046071,936001075,947344218,947496055,950034792,950075297,952669499,954032889,954045943,954066469,962355052,962821095,963019667,963055521,963063283)
ORDER BY 1,2,3;